SUMMARIZE, SUMX not working as expected

[MoodyDirk]

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]

... sorry folks, should have searched before.

The answer is here

 https://community.powerbi.com/t5/DAX-Commands-and-Tips/Summarize-function-providing-wrong-results/m-...

[MoodyDirk · Accepted solution]

... 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]))

[v-xiaosun-msft]

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.