I am trying to create a scorecard in Looker Studio that displays a distinct count of text values from Field A for the previous month (this is updated monthly). I need a distinct count because each value in Field A has several rows of data in the dataset. The value I am looking for is 400 (the distinct count for last month based on the Date field) out of a total of 700, which is the total number of distinct values in Field A for the entire data set.

Does anyone have insights into why my Scorecard won’t display the correct figure?

I have tried to get the Scorecard to display 400 using 2 approaches:

1. Directly build the Scorecard
I inset a Scorecard with the following Setup settings:

• Date Range Dimension = Date
• Metric = count distinct of Field A

At this point I have 700, which is correct because I have not used any date filters yet. However, when I set the Default Date Range filter to Last Month, the scorecard number jumps to 9,000, which is the sum of the distict counts of Field A for each day in the specified date range — not what I am looking for.

To figure out what that 9,000 was, I switched the vizual from a Scorecard to a Table. When I do this I can see a Dimension has been chosen by Looker than does not match Field A, which is the Metric. When I switch this Dimension to Field A it changes the number of rows to 400 — the correct number.At this point, the Scorecard turned Table has the following Setup:

Table Setup settings:

• Date Range Dimension = Date
• Dimension = Field A
• Metric = count distinct of Field A
• Default Date Range = Last month
• Comparison date range = Previous period

Unfortunatly, switching my 400 row Table back to Scorecard still displays 9,000 on the Scorecard.

2. Build the Table first then try to switch to Scorecard
Since building the Scorecard first did not work, I tried building the table first using the SAME setup as the Table Setup above:

Table Setup settings:

• Date Range Dimension = Date
• Dimension = Field A
• Metric = count distinct of Field A
• Default Date Range = Last month
• Comparison date range = Previous period

This results in 400 rows…the correct figure I want from the Scorecard. From here I switched this Table to a Scorecard and the figure changes back to 9,000 again. The Scorecard has the same Setup settings as the first Scorecard from process 1 above:

• Date Range Dimension = Date
• Metric = count distinct of Field A
• Default Date Range = Last month
• Comparison date range = Previous period

I can even remove the Date filters and get back to 700, same as the original Scorecard from process 1 above.

When I switch this Scorecard back to the Table I again get 400.

I cannot figure out why the Scorecard is reaggregating and I cannot tell what it is reaggregating on because the Dimension and Metric in the Table match after I made that change. But this didn’t fix the Scorecard figure.

Thank you in advance for the help!