Total by different Dimension

[Anonymous]

Hi,

I have 2 below visuals, the first visual is giving the correct total = 51 for the measure 'Distinct Counts' and it has a filter which is on the second measure 'Total Counts'>1.

In the second visual I want to see it by state not Job Number, but it gives me wrong total = 2170 with the filter 'Total Counts'>1.

The DAX are as below:
Distinct Counts =
DISTINCTCOUNTNOBLANK( Tow[Job Number])

and

Total Counts = COUNTROWS( Tow)

I know I will need to use sumx with summrize, but I can't get it to work

Can you please help.

[amitchandak · Accepted solution]

@Anonymous , For Distinct Count > 1

Try like

sumx(filter(Values( Tow[Job Number]) , [Distinct Counts] >1),[Distinct Counts])

distinct count can behave differently based on group by

[Anonymous]

Thank you so much @amitchandak , it worked.

Appreicate it.

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please correct me if I wrongly understood your question.

please try to write measure like below.

Measure = 

VAR _newtable = filter ( summarize (table, jobnumber column, "@totalcount", your total count measure) , [@totalcount] >1)
RETURN

sumx (_newtable, [@totalcount])

If it is Ok with you, please share your sample pbix file, then I can try to come up with a

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

more accurate measure.

Thank you.

[Anonymous]

Thanks @Jihwan_Kim , your solution also worked. great.

[amitchandak · Accepted solution]

@Anonymous , For Distinct Count > 1

Try like

sumx(filter(Values( Tow[Job Number]) , [Distinct Counts] >1),[Distinct Counts])

distinct count can behave differently based on group by