Is there a way to add a row at the bottom of a table where that row simply does distinct counts?

[krichmond]

Is there a way to add a row at the bottom of a table (sample of it below) where that row simply distinct counts the number of "Work Orders" and "Spoken Keys"? This row would need to live at the bottom of the data in the table (sample of it below).

Sample Of New Report Without Distinct Counts:

Sample Of Existing Report With Distinct Counts:

[Greg_Deckler · Accepted solution]

@krichmond You would have to construct measures like this:

Measure 2 = IF(HASONEVALUE(Table3[Work Order]),MAX([Work Order]),COUNTROWS(DISTINCT(ALL('Table3'[Work Order])))&"")

[krichmond]

@Greg_Deckler - I tried exporting the data in the table into Excel but those values didn't carry over. Is there a way for those values to carry over into an Excel export?

[krichmond]

@Greg_Deckler - So we figured out the export piece. Sorry to ping you with that. I am seeing that the counts are coming back inaccurate, but they are just a few off. Not sure what is causing this. A link to sample data is here:

• https://www.dropbox.com/s/psuy0r2a789mhqt/Data%20Test%20For%20Graphics%20Report.xlsx?dl=0

The "Work Order" count should have a distinct count of 155 and the "Spoken Key" count should have a distinct count of 161. Right now it is coming over as 156 for the "Work Order" and 166 for the "Spoken Key".

Also, if possible without messing anything up, is there a way to have the distinct counts automatically adjust to filters that are applied to the report? I tried filtering the report and noticed that the numbers stayed static with the adjusting data set.

Based on all of this, are you able to tell what is driving this variance between the actuals and the calculated results? Sorry for the question, very new to DAX and still trying to learn it on the fly. I appreciate your help so far.

[Greg_Deckler]

@krichmond Here is a guess. Trailing spaces and other white space, use Trim and Clean operations to remove anything like that. One other thing to keep in mind. For Power BI data models, the model is in "ignore case" mode. So HBJ24B is the same as hBJ24b is the same as hbj24b is the same as HBj24B, you get the idea. So just something to be aware of when dealing iwth distinct counts of alphanumeric values.