Forum Discussion

MoodyDirk's avatar
MoodyDirk
New Member
3 years ago
Solved

SUMMARIZE, SUMX not working as expected

Hi,

 

my DAX ...

 

Position_Headcount =
VAR Helper_Table = SUMMARIZE('FACT_turnover',FACT_turnover[Position],"Helper_Value",[Position_HeadCount_1])
RETURN
IF(HASONEVALUE(Fact_turnover[Position]),[Position_HeadCount_1],SUMX(Helper_Table,[Helper_Value]))
 
... does not give me the results I expect.
 
A little background:
I've learned (Google Search) that this DAX ...
 
Position_HeadCount_1 =
VAR endterminated = DATE(YEAR(MAX(FACT_turnover[termination_date]))+0, 12, 31)
VAR turnover_position = SELECTEDVALUE(FACT_turnover[Position])
RETURN

CALCULATE(COUNTROWS(FACT_employees),
FACT_employees[status] <> "terminated" && FACT_employees[hired] <= endterminated && FACT_employees[Position] = turnover_position
)
 
... will not provide Subtotals or Grand Totals in a Matrix.
A pssible workaround points to another DAX, utilizing SUMMARIZE into a temp table and then display a SUMX of it's content on Total rows in the matrix.
 
To see what's going on I've added a 3rd DAX ...
Position_Headcount_3 =
VAR Helper_Table = SUMMARIZE('FACT_turnover',FACT_turnover[Position],"Helper_Value",[Position_HeadCount_1])
RETURN
IF(HASONEVALUE(Fact_turnover[Position]),SUMX(Helper_Table,[Helper_value]),SUMX(Helper_Table,[Helper_Value]))
... so the SUMX result is shown in the matrix.
 
The matrix  (first visible, not blacked out) column = DAX Position_Headcount ...

... so, the Total of 24 does not match whats listed, should be 89.

89 is confirmed correct by background MS SQL queries over the same datasource.

Results for column Position_Headcount_3 shows that, what looks like, the SUMMARIZE statement skips rows.

 

I'd appreciate any suggestion or comment why this doesn's work.

 

One clue I found (Google Search) hinted that the new/temp SUMMARIZE table creates a unique identifier on Helper_Value (which is just a number), so I tried this DAX ...

 

Position_Headcount_2 =

VAR turnover_position = SELECTEDVALUE(FACT_turnover[Position])
VAR Helper_Table = SUMMARIZE('FACT_turnover',[Position],"Helper_Position", turnover_position, "Helper_Count",[Position_HeadCount_1])
RETURN

IF(HASONEVALUE(Fact_turnover[Position]),[Position_HeadCount_1],SUMX(Helper_Table, [Helper_Count]))
 
... adding 'Helper_Position' (which is a matrix unique text value) as a second column in the temp table, but the matrix screenshot shows no difference in the result.
 
I don't fully understand why PowerBI makes simple seeming things so complicated.
 
Thanks, Dirk  

 

 
   
 
 
  • MoodyDirk's avatar
    MoodyDirk
    3 years ago

    ... more specific, this DAX works, gives me the correct Total:

     

    Position_Headcount =

    VAR turnover_position = SELECTEDVALUE(FACT_turnover[Position])
    VAR Helper_Table = ADDCOLUMNS( SUMMARIZE('FACT_turnover',[Position],"Helper_Position", turnover_position), "Helper_Count", [Position_HeadCount_1])
    RETURN

    IF(HASONEVALUE(Fact_turnover[Position]),[Position_HeadCount_1],SUMX(Helper_Table, [Helper_Count]))

3 Replies

    • v-xiaosun-msft's avatar
      v-xiaosun-msft
      Icon for Community Support rankCommunity Support
      3 years ago

      Hi MoodyDirk ,

       

      Glad that you have solved the problem by yourself! If you don't have other problems, could you please accept your answer as the solution? Then we are able to close the thread.

       

      Best Regards,
      Community Support Team _ xiaosun

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

    • MoodyDirk's avatar
      MoodyDirk
      New Member
      3 years ago

      ... more specific, this DAX works, gives me the correct Total:

       

      Position_Headcount =

      VAR turnover_position = SELECTEDVALUE(FACT_turnover[Position])
      VAR Helper_Table = ADDCOLUMNS( SUMMARIZE('FACT_turnover',[Position],"Helper_Position", turnover_position), "Helper_Count", [Position_HeadCount_1])
      RETURN

      IF(HASONEVALUE(Fact_turnover[Position]),[Position_HeadCount_1],SUMX(Helper_Table, [Helper_Count]))