Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Distinct sum issue

I need to sum the distinct values in the headcount column and get the sum in  every row in another column , this is some data to work on. i'm using  VAR myheadcount = SUMMARIZE( Table, Table[Hea...
Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    Hi Anonymous ,

     

    You also need to add the year to the filtering:

     

    Measure =
VAR temp_table =
    FILTER (
        SUMMARIZE ( ALL ( 'Table' ); 'Table'[DAte]; 'Table'[Headcount] );
        'Table'[Month] = SELECTEDVALUE ( 'Table'[Month] ) &&
        'Table'[Year] = SELECTEDVALUE ( 'Table'[Year] )
    )
RETURN
    CALCULATE ( SUMX ( temp_table; 'Table'[Headcount] ) )
MFelix's avatar
MFelix
Super User
6 years ago

Hi Anonymous ,

 

You can try the following measure:

 

Measure =
VAR temp_table =
    FILTER (
        SUMMARIZE ( ALL ( 'Table' ); 'Table'[DAte]; 'Table'[Headcount] );
        'Table'[DAte] = SELECTEDVALUE ( 'Table'[DAte] )
    )
RETURN
    CALCULATE ( SUMX ( temp_table; 'Table'[Headcount] ) )

 

Be aware that you don't refer if the date is based I have made an example with all dates being the same for the same period, but you can change the filtering to add the month / year if you have those columns instead of the date.

 

check PBIX file attach.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    MFelix ,

     

     

    I just replaced date with month in my table. It's not summing up correctly

    • MFelix's avatar
      MFelix
      Super User
      6 years ago

      Hi Anonymous ,

       

      You also need to add the year to the filtering:

       

      Measure =
VAR temp_table =
    FILTER (
        SUMMARIZE ( ALL ( 'Table' ); 'Table'[DAte]; 'Table'[Headcount] );
        'Table'[Month] = SELECTEDVALUE ( 'Table'[Month] ) &&
        'Table'[Year] = SELECTEDVALUE ( 'Table'[Year] )
    )
RETURN
    CALCULATE ( SUMX ( temp_table; 'Table'[Headcount] ) )