Dax measure, Weighted age average, how to recalculate rows?

[Anonymous]

Hello community,

Below is an example of my current matrix. I have managed to calculated the weighted Age Days on the transactions correctly. IE transaction 297315 as 37.85 weighted age days. However the totals on both Matter (4688) and Client (250) are both not calculating correctly.

An example:

Matter 4688 weighted age days should be :  (Aged Days * Portion of cost) (all transactions)

So ( 80days * 47.3% + 80 days * 49.8% +73 days * 2.8%) = 79.8 Weighted Age days instead of the current 120.49 days.

Second example but with Client 250

Currently the Client 250 average weighted days is 287 when it should be 67.3 Days as calculated using the above calculations. (All transactions under client 250.

Measure to calculate Cost:

Measure_Cost_Close = 
VAR MaxDate = MAX ( 'Date'[Date] )
var CostSUM = CALCULATE(Sum(CostHistory[Cost]),FILTER(ALL('Date'),'Date'[Date]<= MaxDate))
return
IF (
 HASONEVALUE ( CostHistory[Tran_ID] ),
 COSTSUM,
 CALCULATE(CostSUM,ALLSELECTED(CostHistory))
)

Measure to calculation Total Cost for a transaction

Measure_Total_Cost_ClientMatterTran =

VAR grandtotal = Calculate(sum(CostHistory[Cost]),ALLSELECTED(CostHistory))

VAR ClientSum =
Calculate([Measure_Cost_Close],ALLSELECTED(CostHistory[Client_ID]))
VAR ClientSelected =
ISINSCOPE ( CostHistory[Client_ID] )
VAR MatterSum =
Calculate([Measure_Cost_Close],ALLSELECTED(CostHistory[Matter_ID]))
VAR MatterSelected =
ISINSCOPE ( CostHistory[Matter_ID] )
VAR TranSum =
Calculate([Measure_Cost_Close],ALLSELECTED(CostHistory[Tran_ID]))
VAR TranSelected =
ISINSCOPE (CostHistory[Tran_ID] )

RETURN
SWITCH (
TRUE (),
TranSelected, TranSum,
MatterSelected, MatterSum,
ClientSelected, ClientSum,
grandtotal
)

Measure for calculating the Weighted Age Days

Measure_Aged_Days_By_Portion = 
var total = [Measure_Aged_Days] * ([Measure_Cost_Close]/[Measure_Total_Cost_ClientMatterTran])
return
total

Any suggestions would be greatly appreciated? i am truly stuck on this total aggregation..

[v-lid-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[Anonymous]

This is the fix i found below which gave me the correct weighted average.

var total =CALCULATE(Sum(WIPHistory[WIP]),ALLSELECTED(WIPHistory[Alloc_ID]))

return

SUMX(FILTER(WIPHistory,WIPHistory[Matter_Is_Balanced_Flag] = 0),CALCULATE(DIVIDE(Sum(WIPHistory[WIP]),total) * AVERAGE(WIPHistory[WIPDays])))

I made one other measure to try make it a tiny bit faster, but in the end even with the correct answer it was not fast enough. It might be an issue with my SSAS tabular server but its just so slow that Powerbi Just processes the matrix forever untill it hits an out of memory error which takes ages.My Fact table of 15 million and the data is shown in a matrix of Clients and Matters (over 400,000 matters). The matrix works fine at the client level and it loads almost instantly, but when matrix is drilled down to Matter it just spins indefinitely. 

At this stage i have figured that i dont have a memory issue with my VM with 24 gb of ram and i have any network/security issues blocking the request and my tabular model is a star schema. So its not related to Powerbi, i may have to go back to MDX and cubes for this operation.

Even with other measures Simple calculations looking at the fact table (15 million) and the Dimension table (400k) just spin forever (only in matrixies) however my firm only use matrixies for analaysis. The operations are almost instant but after you drill down enough in powerbis matrix it just spins.

[Anonymous]

I managed to get one step closer, but still have a little issue.

I changed my final measure and its all looking good now expect for one count. No idea why

Measure_Aged_Days_By_Portion = 
var total = [Measure_Aged_Days] * ([Measure_Cost_Close]/[Measure_Total_Cost_ClientMatterTran])
return
IF (
 HASONEVALUE ( CostHistory[Tran_ID] ),
 total,
 SUMX(ALLSELECTED(CostHistory[Tran_ID]),[Measure_Aged_Days] * ([Measure_Cost_Close]/[Measure_Total_Cost_ClientMatterAlloc])) 
)

26 should be 54.

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

Sorry for our late reply , We can try to use the following measure to meet your requirement:

Measure_Aged_Days_By_Portion =
SUMX (
    DISTINCT ( CostHistory[Tran_ID] ),
    [Measure_Aged_Days] * ( [Measure_Cost_Close] / [Measure_Total_Cost_ClientMatterTran] )
)

Best regards,