Weighted Average dax measure - Totals are incorrect

[Anonymous]

I have a matrix that looks like this:

WIP is just a total on WIP from a transaction.

Age is a column which states the age of the wip

Total WIP is used in the calculation

WIP Percent Calculates the WIP to total WIP        (WIP  * total WIP)

Weighted Aged Days: Calculates with the following (WIP Percent) * Age. However in the Matter and Client totals of the Matrix the Total is around 200 when it should be 23.

Measure i am using for Weighted Aged Days:

Measure_Total_Aged_Days =
var TotalWIP = Calculate(Sum(WIPHistory[WIP]),ALLSELECTED(WIPHistory[Alloc_ID]))
Var WIP= Calculate(Sum(WIPHistory[WIP]))
var WipAge= [Aged Days]
var Complete = (DIVIDE(WIP,TotalWIP ,0) * WipAge)

return
IF (
HASONEVALUE ( WIPHistory[CLIENT_ID] ),
Complete,
SUMX ( ALLSELECTED (WIPHistory[CLIENT_ID] ),Complete )
)

Is it an issue with my Measure? any help at all would be amazing.

Client ID/MatterID/Tran	WIP	Age	Total WIP	WIP Percent	Weighted Aged Days
43	100	60	100	100%	200 (WRONG TOTAL)
46510	100	60	100	100%	200
551	50	30	100	50%	15
552	30	20	100	30%	6
553	30	20	100	20%	2

[v-lili6-msft · Accepted solution]

HI @Anonymous 

This is a measure totals problem. you may add another measure as below:

Measure =
VAR _table =
    SUMMARIZE (
        WIPHistory,
        WIPHistory[Client ID],
        WIPHistory[MatterID],
        WIPHistory[Tran],
        "_Value", [Measure_Total_Aged_Days]
    )
RETURN
    SUMX ( _table, [_Value] )

If you still have the problem, please share a simple sample pbix file, that will be a great help.

Regards,

Lin

[Greg_Deckler]

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[Anonymous]

I am currently using Sumx and HasOneValue and the calculation still does not total correctly.

[Greg_Deckler]

Right, but you are probably not summarizing it the same way that you are in the table visualization so it likely comes out wrong. Or it could be an issue with your measure the way you have written it since it is in an individual row.

Tough to say without sample source data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Thank you! i will look into this summerize function.

[Anonymous]

What i have tried:

var total2 = sumx(WIPHistory,[Aged Days]) * (SUMX(WIPHistory,WIPHistory[WIP]) / Calculate(SumX(WIPHistory,[WIP]),ALLSELECTED(WIPHistory[Alloc_ID])))

var a = SUMX(SUMMARIZE(WIPHistory,WIPHistory[Alloc_ID],"b",total2),[b])

return

IF (

HASONEVALUE ( WIPHistory[Alloc_ID] ),

a,

SUMX ( ALLSELECTED (WIPHistory[Alloc_ID] ),a )

)

This gets the correct row level but the totals are still out. I will continue to try get this totalled and post updates