Forum Discussion

geirselvag's avatar
geirselvag
Frequent Visitor
1 year ago
Solved

DAX help - distributed sum

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...
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
    geirselvag
    1 year ago

    This is excellent work.
    Thank you!

    Kind regards,
    Geir

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
geirselvag
Frequent Visitor
1 year ago

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's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      geirselvag
      Frequent Visitor
      1 year ago

      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's avatar
        Anonymous
        Not applicable
        1 year ago

        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.