topic DAX help - distributed sum in DAX Commands and Tips

DAX help - distributed sum

geirselvag — Wed, 11 Dec 2024 14:48:00 GMT

Hello folks,

The DAX requirements I'm faced with is include to be able to distribute project cost (or settlement) to other project based on precentage. The basis for DAX is two tables one dim_WBS dimension table, and one fact_Cost transactions table were relationship one-to-many by a WBS id.

Tables:

dim_WBS

fact_COST

This table include column which is from Excel calculated column, and the number should be calculated by DAX.

Current DAX:

I have tried other DAX but gives wrong result. Let me know where am wrong on this code.
Professional DAX people, your help is highly appreciated.

Best regards,

gselvag

Re: DAX help - distributed sum

johnt75 — Wed, 11 Dec 2024 15:48:05 GMT

You can use RELATED to get the value of column on the one-side of a relationship when you have a row context on the many-side.

Distributed cost = SUMX ( fact_Cost, fact_Cost[Cost] * ( 1 + RELATED ( dim_wbs[Percent] ) ) )

Re: DAX help - distributed sum

Anonymous — Thu, 12 Dec 2024 02:41:55 GMT

Hi geirselvag

You can create a calculated column in fact_COST table with the following DAX:

Cost_Distributed = VAR NewWBS = IF(RELATED(dim_WBS[Settlement_WBS]) = BLANK(), fact_COST[WBS], RELATED(dim_WBS[Settlement_WBS])) VAR OriginalCost = LOOKUPVALUE(fact_COST[Cost], fact_COST[WBS], NewWBS) VAR Distributed = OriginalCost * RELATED(dim_WBS[Percent]) RETURN IF(fact_COST[WBS] = NewWBS, 0, fact_COST[Cost] + Distributed)

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: DAX help - distributed sum

geirselvag — Thu, 19 Dec 2024 14:59:44 GMT

Thanks, but calculated column is not what am looking for. The solution to my issue should be solved as a DAX measure.

Best regards,
Geir

Re: DAX help - distributed sum

Anonymous — Fri, 20 Dec 2024 01:52:51 GMT

Hi geirselvag

Please try to create the following measure:

Cost_Distributed_Measure = VAR CurrentWBS = SELECTEDVALUE(fact_COST[WBS]) VAR CurrentCOST = SELECTEDVALUE(fact_COST[Cost]) VAR NewWBS = IF( ISBLANK(LOOKUPVALUE(dim_WBS[Settlement_WBS], dim_WBS[WBS], CurrentWBS)), CurrentWBS, LOOKUPVALUE(dim_WBS[Settlement_WBS], dim_WBS[WBS], CurrentWBS) ) VAR OriginalCost = CALCULATE( SUM(fact_COST[Cost]), fact_COST[WBS] = NewWBS, REMOVEFILTERS(fact_COST) ) VAR Distributed = OriginalCost * LOOKUPVALUE(dim_WBS[Percent], dim_WBS[WBS], CurrentWBS) RETURN IF(ISBLANK(Distributed), 0, Distributed + CurrentCOST)

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: DAX help - distributed sum

geirselvag — Fri, 20 Dec 2024 07:45:59 GMT

Thanks Jarvis, the measure works fine with one row level. I need this to work with subtotal, as there will be between 1-5 levels in the structure. However the measure works perfect on rowlevel only thing missing is the subtotal and grantotal marked in red.

Re: DAX help - distributed sum

Anonymous — Wed, 25 Dec 2024 08:46:50 GMT

Hi geirselvag

Sorry for the late reply.

Try to update the measure with the following DAX:

Cost_Distributed_Measure = SUMX( fact_COST, VAR CurrentWBS = fact_COST[WBS] VAR CurrentCOST = fact_COST[Cost] VAR NewWBS = IF( ISBLANK(LOOKUPVALUE(dim_WBS[Settlement_WBS], dim_WBS[WBS], CurrentWBS)), CurrentWBS, LOOKUPVALUE(dim_WBS[Settlement_WBS], dim_WBS[WBS], CurrentWBS) ) VAR OriginalCost = CALCULATE( SUM(fact_COST[Cost]), fact_COST[WBS] = NewWBS, REMOVEFILTERS(fact_COST) ) VAR Distributed = OriginalCost * LOOKUPVALUE(dim_WBS[Percent], dim_WBS[WBS], CurrentWBS) RETURN IF(ISBLANK(Distributed), 0, Distributed + CurrentCOST) )

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: DAX help - distributed sum

geirselvag — Mon, 06 Jan 2025 12:05:29 GMT

This is excellent work.
Thank you!

Kind regards,
Geir