DAX help - distributed sum

[geirselvag]

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

[Anonymous · Accepted solution]

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.

[Anonymous]

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.

[geirselvag]

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

[Anonymous]

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.

[geirselvag]

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.

[Anonymous · Accepted solution]

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.

[geirselvag · Accepted solution]

This is excellent work.
Thank you!

Kind regards,
Geir

[johnt75]

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] ) ) )