Forum Discussion

mail2vjj's avatar
mail2vjj
Icon for Helper III rankHelper III
8 years ago

Calculation using PATH function Hierarchy

Hello everyone,   I am trying to do a calculation based on a hierarchy.   I have a table with Names and their Parents and I have created a hierarchy using the PATH function.   I have sales data...
Need Help
Show and Tell
Tips and Tricks
mail2vjj's avatar
mail2vjj
Icon for Helper III rankHelper III
8 years ago

Hi everyone,

I am trying to come up with a DAX code for my problem and this is where I have reached so far.

Thinking of my problem in DAX terms, for calculation of the commission 2 conditions need to be satisfied.
1. The Path should contain the name of the parent for which we are calculating the commission.
2. The Hierarchy Depth of the parent should be less than the child, on the basis of which the commission percentage will be decided and the commission will be calculated.

This is the code that I have come up with so far, and obviously it is not working, because for some reason I do not understand, the EARLIER function is not working.

Commission 2 = IF(
AND(
CONTAINS(Sales, Sales[Name_Path], (Sales[Name])),
Sales[Hierarchy_Depth] < Sales[Hierarchy_Depth]), Sales[Sales]*0.05, 0)

If someone can explain to where am I going wrong or what can be done to get it right, please do let me know.

Any help is appreciated.

 

Thank you,

 

Vishesh Jain

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Hi mail2vjj

     

    Please try this MEASURE

     

    Commission1 =
VAR myparent =
    SELECTEDVALUE ( Sales[Name] )
VAR Children_ =
    ADDCOLUMNS (
        CALCULATETABLE (
            VALUES ( 'Hierarchy'[Name] ),
            FILTER ( ALL ( 'Hierarchy' ), 'Hierarchy'[Parent] = myparent )
        ),
        "MySales", CALCULATE (
            SUM ( Sales[Sales] ),
            FILTER ( ALL ( Sales ), Sales[Name] = EARLIER ( 'Hierarchy'[Name] ) )
        )
    )
RETURN
    SUMX ( Children_, [MySales] ) * .05
        + SUM ( Sales[Sales] ) * .1
    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

      mail2vjj

       

      I am not sure if above is the best way to do it...
      But atleast it works with your sample data:smileywink:

       

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago

        mail2vjj

         

        To get Commission 2, you can try this MEASURE

         

        Commission_2 =
VAR myparent =
    SELECTEDVALUE ( Sales[Name] )
VAR DC =
    ADDCOLUMNS (
        ADDCOLUMNS (
            FILTER (
                ALL ( 'Hierarchy' ),
                PATHCONTAINS ( 'Hierarchy'[Name Path], myparent )
            ),
            "ParentPosition", FIND ( myparent, SUBSTITUTE ( 'Hierarchy'[Name Path], "|", "" ), 1, 0 )
        ),
        "Direct Child", MID ( SUBSTITUTE ( 'Hierarchy'[Name Path], "|", "" ), [ParentPosition] + 1, 1 )
    )
VAR IDC =
    ADDCOLUMNS (
        ADDCOLUMNS (
            DC,
            "IndirectChild1", MID ( SUBSTITUTE ( 'Hierarchy'[Name Path], "|", "" ), [ParentPosition] + 2, 1 )
        ),
        "IndirectChild2", MID ( SUBSTITUTE ( 'Hierarchy'[Name Path], "|", "" ), [ParentPosition] + 3, 1 )
    )
VAR DirectChildren =
    ADDCOLUMNS (
        DISTINCT ( SELECTCOLUMNS ( DC, "DC", [Direct Child] ) ),
        "Mysales", CALCULATE ( SUM ( Sales[Sales] ), FILTER ( ALL ( Sales ), Sales[Name] = [DC] ) )
    )
VAR IndirectChildren =
    ADDCOLUMNS (
        DISTINCT (
            UNION (
                SELECTCOLUMNS ( IDC, "IDC", [IndirectChild1] ),
                SELECTCOLUMNS ( IDC, "IDC", [IndirectChild2] )
            )
        ),
        "Mysales", CALCULATE (
            SUM ( Sales[Sales] ),
            FILTER ( ALL ( Sales ), Sales[Name] = [IDC] )
        )
    )
RETURN
    SUMX ( IndirectChildren, [Mysales] ) * .01
        + SUMX ( directChildren, [Mysales] ) * .05
        + SUM ( Sales[Sales] ) * .1