The total is coming incorrect.

[vishu263]

@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.

[tamerj1 · Accepted solution]

Hi @vishu263 

please try

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

[tamerj1]

Hi @vishu263 

Please try

Pos FTE Correct Total =
SUMX (

VALUES ( 'Table'[Position] ),

[Pos FTE] 

)

[vishu263]

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

[tamerj1]

Hi @vishu263 
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
)

[vishu263]

Thanks for your response @tamerj1 .

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

[tamerj1]

@vishu263 

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