weighted average / sum product in DAX fix grand totals

[dpombal]

Hi all,

I have a table with 32 processes (rows) having 2 different status ( denied and accepted),

There are 2 direct measures ( sum_days_of duration and number of processes)

My question is about calculating the average of duration days weighted in the following way ( My measure fails on calculating totals, see below pbix)

Here it is my current pbix (download link here)

sum_of_days = sum(Data[days])

Count_Of_Processes = DISTINCTCOUNT(Data[IdProcess])

Avg_Days = DIVIDE([sum_of_days];[Count_Of_Processes]) --> Fails on totals for my usecase i need a weighted like before sumproduct formulae

I am lookinf for 16,99 in totals instead of 3,41

Thanks in advance

[Zubair_Muhammad · Accepted solution]

Hi @dpombal

Try this revision

Avg_Days =
IF (
    HASONEVALUE ( Data[Status] ),
    DIVIDE ( [sum_of_days], [Count_Of_Processes] ),
    DIVIDE (
        SUMX ( ALL ( Data[Status] ), [sum_of_days] * [Count_Of_Processes] ),
        SUM ( Data[days] )
    )
)

[Zubair_Muhammad · Accepted solution]

Hi @dpombal

Try this revision

Avg_Days =
IF (
    HASONEVALUE ( Data[Status] ),
    DIVIDE ( [sum_of_days], [Count_Of_Processes] ),
    DIVIDE (
        SUMX ( ALL ( Data[Status] ), [sum_of_days] * [Count_Of_Processes] ),
        SUM ( Data[days] )
    )
)

[Zubair_Muhammad · Accepted solution]

@dpombal