Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Total by different Dimension

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.

 

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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

  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    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.

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks Jihwan_Kim , your solution also worked. great.