Show grand total only of unique values

[Anonymous]

Hi,

I have below kind of data. I want to show grand total only of the uniquhe values per FN. Any help is appreciated. Thanks.

FN	LocNo	Count
1	5	1
2	10	1
	58	1
Grand Total		2

[dax · Accepted solution]

Hi @Anonymous , 

You could try below measure

Measure 4 = IF(HASONEVALUE(t3[FN]), DISTINCTCOUNT(t3[FN]), CALCULATE(DISTINCTCOUNT(t3[LocNo]), t3[FN]<>BLANK()))

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Grand total should be displayed as 2 because the unique values by FN is 2. Generally, total is 3 but it should be displayed as 2 even if total is 3.

[DataZoe]

Hi @Anonymous, have you tried using the measure No Count = DISTINCTCOUNT(TableName[FN])? 

[DataZoe]

Ah I see! Have you tried No Count = DISTINCTCOUNTNOBLANK(TableName[FN])?

[Anonymous]

@DataZoe Thanks for your reply but sorry it didn't work. Only grand total should have distinct but at the subtotal level it shouldn't be. I tried with sumx but somehow I didn't succeed.

[Tahreem24]

@Anonymous ,

Let me know is this your expected output?

[Anonymous]

thanks @Tahreem24 for your efforts but in measure1 there will be 1 1 1 in each row so total will be 3 ideally but it will show 2. Basically, I want that only grand total should be of unique values. 

[Tahreem24]

@Anonymous ,

Not exact getting your query. PLease share some more deets.

But if you want get the unique value so Use DISTINCT(TableName[ColumnName])

[AllisonKennedy]

Not sure what you mean by unique values? Can you provide more info on your raw data please?

You can add a filter for FN <>BLANK() or FN not null