topic The total is coming incorrect. in DAX Commands and Tips

The total is coming incorrect.

Anonymous — Tue, 16 May 2023 07:23:20 GMT

member, I am using the below measure to divide the sum i.e. _FTE by _countRows. The VAR_FTE gives me 9.85. VAR_countRows gives me 10.

I want to see the total of VAR_divFTE as 2.85 i.e. 0.85+1+1. However, it is dividing (=9.85/10) and giving me total as 0.985.

Can someone please suggest what changes needs to be done in VAR_divFTE to get the total of 2.85

NOTE : I am using Power BI dataset as a source here and cannot resolve this issue by creating calculated column, writing a DAX meausre is the only option.

Re: The total is coming incorrect.

tamerj1 — Tue, 16 May 2023 07:35:51 GMT

Hi Anonymous

Please try

Pos FTE Correct Total =
SUMX (

VALUES ( 'Table'[Position] ),

[Pos FTE]

)

Re: The total is coming incorrect.

Anonymous — Tue, 16 May 2023 08:22:32 GMT

Hi tamerj1 ,

Where exactly are you suggesting to make this change?

I am putting the text version of the above DAX to make it easier.

Pos FTE =

VAR _countRows = COUNTA('Position and People Establishment'[Position])

VAR _FTE = SUM('Position and People Establishment'[Establishment Count])

VAR _divFTE = IF(_countRows > 1, DIVIDE(_FTE, _countRows), _FTE)

return _divFTE

Re: The total is coming incorrect.

tamerj1 — Tue, 16 May 2023 12:30:14 GMT

Hi Anonymous
Apologies for the late reply. Please try

Pos FTE = SUMX ( VALUES ( 'Position and People Establishment'[Position] ), VAR _Table = CALCULATETABLE ( 'Position and People Establishment' ) VAR _CountRows = COUNTROWS ( 'Position and People Establishment' ) VAR _FTE = SUMX ( _Table, 'Position and People Establishment'[Establishment Count] ) VAR _DivFTE = DIVIDE ( _FTE, _CountRows ) RETURN _DivFTE )

Re: The total is coming incorrect.

Anonymous — Tue, 16 May 2023 23:24:09 GMT

Thanks for your response tamerj1 .

I tried using the above DAX however, it is giving the same total count of 0.985

Re: The total is coming incorrect.

tamerj1 — Wed, 17 May 2023 03:33:47 GMT

Anonymous

It should work based on your description and presented visual. Unless both have been over simplified.

Re: The total is coming incorrect.

tamerj1 — Sun, 02 Jul 2023 09:15:11 GMT

Hi Anonymous

please try

divide_pos =
SUMX ( VALUES ( 'Table'[Position] ), DIVIDE ( [Sum_Pos], [Cnt PoS] ) )