Calculation using PATH function Hierarchy

[mail2vjj]

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 in another table and from that data based on the hierarchy I am trying to calculate the commission.

This is the picture of my hierarchy:

 These are all the Paths that I have created and the link to the files is at the end of the message.

This is my Sales Table:

Date	Name	Sales
01-01-18	A	10
01-01-18	B	20
01-01-18	C	50
01-01-18	D	30
01-01-18	E	40
01-01-18	F	60
01-01-18	G	20
01-01-18	H	30
01-01-18	I	60
02-01-18	A	20
02-01-18	B	40
02-01-18	C	30
02-01-18	D	50
02-01-18	E	10
02-01-18	F	20
02-01-18	G	30
02-01-18	H	30
02-01-18	I	20

This is my Total Sales Table along with the Desired Commission results:

Name	Sales	Commission 1	Commission 2
A	30	10	14
B	60	12.5	13.9
C	80	14.5	14.5
D	80	15	11
E	50	5	5
F	80	8	8
G	50	5	5
H	60	6	6
I	80	8	8

I have 2 ways to calculate commission.

First:

Commission on own sale (10%) + Commission for every sub-branch of the hierarchy (5%).

For eg. For B, own sale is 60 and sub-branch sales for D and E are 80 & 50 respectively, so total is 130.

So Commission for B is (60*10%) + (130*5%) = 12.5

For eg. For D, own sale is 80 and sub-branch sales for H and I are 60 & 80 respectively, so total is 140.

So Commission for B is (80*10%) + (140*5%) = 15

Second:

Commission on own sale (10%) + Commission from every sub-branch (5%) and sub-sub branches of the hierarchy (1%).

For Eg. For A, own sale is 30, sub-branch sales for B and C are 140 and all other sub-sub-branches (D,E,F,G,H,I) total is 400

So Commission for A is (30*10%) + (140*5%) + (400*1%) = 14

Similarly Commission for B, Own Sale is 60, sub-branch sales for D and E are 80 & 50 respectively and sub-sub-branch sale for H and I are 60 and 80 respectively.

So Commission for B is (60*10%) + (130*5%) + (140*1%) = 13.9

Ths following is the link to my Excel file and the PBIX file.

https://1drv.ms/f/s!Ap0qSKP-4qpThCc1sSXanmibgIff

It would be great if anyone can help me with this.

Thank you,

Vishesh Jain

[mail2vjj]

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]

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]

@mail2vjj

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

[Zubair_Muhammad]

@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

[mail2vjj]

Hi @Zubair_Muhammad,

First of all a huge thanks for taking the pain yet again to write that mammoth code.

Your code is working but only to a certain extent.

As you can see in both Commission 1 & 2, the calculation of the commission is correct but at the grand total level, there is a mistake.

This is what the totals should be and the code shows, Commission_1 = 58.5 and Commission_2 = 57, which are not correct.

Name	Sales	Commission_1	Commission_2
A	30	10	14
B	60	12.5	13.9
C	80	14.5	14.5
D	80	15	15
E	50	5	5
F	80	8	8
G	50	5	5
H	60	6	6
I	80	8	8
Total	570	84	89.4

Also, I am trying to use a Hierarchy slicer on the table and the measures that you sent and it is showing me number is places, that it shouldn't.

In the following picture, when I select the B hierarchy, it is still showing me 1.50 for A, C, F & G.

Again, after selecting the B hierarchy, the grand total is wrong, For commission 1 it should be 46.5 (12.5+15+5+6+8) and commission 2 should be 47.9 (13.9+15+5+6+8).

But as you can see in the following picture, the totals are wrong again and it is still showing me values for other names even after the slicer is selected.

So can you please help fixing this and meanwhile if I come up with something, I will post about it.

I am updating the files in OneDrive as well.

https://1drv.ms/f/s!Ap0qSKP-4qpThCc1sSXanmibgIff

Again, thank you for taking the effort to write all that code.

Vishesh Jain

[Zubair_Muhammad]

@mail2vjj

The problem of incorrect totals can be resolved using these additional MEASURE

Com1 =
IF (
    HASONEFILTER ( Sales[Name] ),
    [Commission_1],
    SUMX ( ALLSELECTED ( Sales[Name] ), [Commission_1] )
)

Com2 =
IF (
    HASONEFILTER ( Sales[Name] ),
    [Commission_2],
    SUMX ( ALLSELECTED ( Sales[Name] ), [Commission_2 )
)

[mail2vjj]

Hi @Zubair_Muhammad

Thanks for taking the time to help me out.

I tried the solution you suggested and it is working at the grand total level but when I use the date slicer on it, the figures are not right on either level.

I tried to add a HASONEFILTER(Sales[Date]) in the IF condition, in the solution that you last suggested, but it didn't work.

I also tried to create a calculated column for the commission, so that the date filter could work on it, but again I failed.

I used the approach you used to calcualte Commission_2 and created Commission New, to use in the Com1 measure. In that I am getting the desired result in the table along with the Hierarchy Slicer, but when I use the date slicer on it, the figures go wrong.

The WRONG total Commission, shown 1st and 2nd Jan are 59 and 52 respectively, which totals to 111, but when there is no date slicer, the RIGHT total commission is 84.

Sorry for all the hassle and thanks for all your help.

I have updated the files on the OneDrive.

https://1drv.ms/f/s!Ap0qSKP-4qpThCc1sSXanmibgIff

Thank you,

Vishesh Jain

[Zubair_Muhammad]

@mail2vjj

Please try this revision.

Just replaced ALL with ALLEXCept to remove date filter

CHange highlighted in RED FONT

Commission_1 New =
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 DirectChildren =
    ADDCOLUMNS (
        DISTINCT ( SELECTCOLUMNS ( DC, "DC", [Direct Child] ) ),
        "Mysales", CALCULATE (
            SUM ( Sales[Sales] ),
            FILTER ( ALLEXCEPT ( Sales, Sales[Date] ), Sales[Name] = [DC] )
        )
    )
RETURN
    + SUMX ( directChildren, [Mysales] ) * .05
        + SUM ( Sales[Sales] ) * .1

[Zubair_Muhammad]

@mail2vjj

Now we get 47.5+36.5=84

[mail2vjj]

Hi @Zubair_Muhammad

Sorry for being such a pain.

I tried your new suggested code and it worked, but then it ran into another problem.

As soon as I put it on a Matrix, with dates in the column, the same issue rose again.

So as you can see, when I select the date on the slicer, the normal table works fine.

But when I put it on a matrix, the calculations go haywire again.

I tried to create a Calendar table and change the code to work on the calendar table in the ALLEXCEPT change that you suggested, but it didn't work.

I even tried to keep the code same and created a relationship between the Sales the Calendar tables, still nothing worked.

I hope you can help me with this.

Also I wanted to know, if I add more details to the sales table, like location or product details and want to see the commission based on that, can I just modify the ALLEXCEPT to add other categories, like you did with the date?

I have updated the files on OneDrive.

https://1drv.ms/f/s!Ap0qSKP-4qpThCc1sSXanmibgIff

Thank you,

Vishesh Jain

[Zubair_Muhammad]

Hi Vishesh

I will check it after few hours
Am out of office

[Zubair_Muhammad]

Hi @mail2vjj

Sorry I had forgotten about your matter

When I use the date from Sales Table,, it gives me correct results.

Please see the image below

[mail2vjj]

Hi @Zubair_Muhammad

Sorry I was not in the office yesterday so wasn't able to reply.

Yes, when I use the dates from the sales table they work, but not when I am using a hierarchy on the Sales dates.

That is where the figures go wrong. I want to be able to use a drill down on that table.

Also, I was trying to create a relationship between the sales and the Calendar tables, so that later on I can use the dates from the Calendar table itself. But when I use the dates from the Calendar table, the figures are not right.

So yes, if we use the dates normally they are working, but not when we use the dates as a hierarchy.

Thank you,

Vishesh Jain

[Zubair_Muhammad]

Hi @mail2vjj

If you add Calendar[Date] to ALLEXCEPT in the Code.....you can then use calendar[date] in Matrix Column as well

See the file attached..

New =
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 DirectChildren =
    ADDCOLUMNS (
        DISTINCT ( SELECTCOLUMNS ( DC, "DC", [Direct Child] ) ),
        "Mysales", CALCULATE (
            SUM ( Sales[Sales] ),
            FILTER (
                ALLEXCEPT ( Sales, Sales[Sales Date], 'Calendar'[Date] ),
                Sales[Name] = [DC]
            )
        )
    )
RETURN
    + SUMX ( directChildren, [Mysales] ) * .05
        + SUM ( Sales[Sales] ) * .1

[mail2vjj]

Hi @Zubair_Muhammad,

I made the changes are you suggested, but it still does not solve the problem of using a 'Date Hierarchy' as in 'year-month-date'.

On the Individual day level the figures work fine, but as soon as I use the Date Hierarchy, the figures go wrong again.

I have one more question now.

As you can see in the picture below, I tried to use the 'Calendar'[Date].[Date] (without the hierarchy) and the figures go wrong, but when I use just, 'Calendar'[Date], which is what you suggested, the figures are correct.

What is the difference, when using [Date].[Date] and when using only [Date] ?

Thank you,

Vishesh Jain

[mail2vjj]

Hi @Zubair_Muhammad,

I have run into another problem.

Even if we leave the dates individually and concentrate only on the months, then as well the commission calculations go wrong.

I tried to enter new data but in that I put in sales ONLY for a couple of names.

So as you can see for the month of February, B and J are the only ones with sales.

So commission for the parents should have been calcualted, but in this case, it is not being calculate for A or H.

Where are we going wrong here?

Thank you,

Vishesh Jain

[Zubair_Muhammad]

Hi @mail2vjj

Please try this revision

New =
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 DirectChildren =
    ADDCOLUMNS (
        DISTINCT ( SELECTCOLUMNS ( DC, "DC", [Direct Child] ) ),
        "Mysales", CALCULATE (
            SUM ( Sales[Sales] ),
            FILTER ( ALLSELECTED ( Sales[Name] ), Sales[Name] = [DC] )
        )
    )
RETURN
    + SUMX ( directChildren, [Mysales] ) * .05
        + SUM ( Sales[Sales] ) * .1

[mail2vjj]

Hi @Zubair_Muhammad,

Yes it works!

The Date hierarchy is working.

Now the only issue that remains is of the commission calculation.

As you can see, the parents are not getting commissions for their child's sales, if they do not have sales on the same date.

For eg. For the sales of B and J, A and H respectively should be getting 5% commissions each, even when A and H do not have sales themselves.

Thank you,

Vishesh Jain

[Zubair_Muhammad]

@mail2vjj

Please could you send me this latest file

[mail2vjj]

Hi @Zubair_Muhammad

Here is the link to the latest files.

https://1drv.ms/f/s!Ap0qSKP-4qpThCc1sSXanmibgIff

Also can you please check, the totals at the grand total level seem off.

For eg. For A, it should be 10 and it is showing 12.5.

For the Grand totals, it is 84 + 7 = 91 and is it showing 94.5.

Thank you,

Vishesh Jain