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

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.

      • geirselvag's avatar
        geirselvag
        Frequent Visitor
        1 year ago

        This is excellent work.
        Thank you!

        Kind regards,
        Geir