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 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'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

7 Replies

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

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