Calculation using PATH function Hierarchy

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

Hi mail2vjj

Could you add rows with zero sales for Names missing in a month?

This will resolve the issue...of getting share for parent when they dont have sales but their children have it

For example I just added the last row in image below and I start getting A's share for B which is the Direct Child

Hi Zubair_Muhammad

I am afraid I cannot do that.

This is the sales sheet and there will only be entries for the actual sales made.

This sheet will be automatically generated by a software.

Also adding 0 sales to my sheet for every single name that exists, will add rows to excel that we don't really need.

Morever, after a certain number of rows, excel slows down and becomes inefficient, so I don't know how wise it will be if we add rows that we don't need.

I am sure you are aware of this.

Sorry for throwing another stone in your way.

Vishesh Jain.

mail2vjj

OK....I will check an alternate way

Is there some way to add the rows with 0 sales using the Query Editor?

Hi mail2vjj

Sorry for late reply

One way of adding the missing row is using a Calculated Table

See the attached file.

Then you have to do all the MEASURES based on this new Table

SalesNew =
VAR mytable =
    CROSSJOIN (
        SELECTCOLUMNS ( Sales, "Name", [Name] ),
        SELECTCOLUMNS (
            Sales,
            "date", [Date],
            "Location", [Location],
            "Product", [Product]
        )
    )
RETURN
    ADDCOLUMNS (
        mytable,
        "Sales", LOOKUPVALUE (
            Sales[Sales],
            Sales[Date], [date],
            Sales[Location], [Location],
            Sales[Name], [Name],
            Sales[Product], [Product]
        )
    )

Hi Zubair_Muhammad

Thanks for hanging in there with me until now and taking the time to work on my problem.

I just saw your solution and I see some problems with it.

One is, as even you know, the model is getting unnecessarily big, since it is creating a row for every single combination of Date, Product, Location and Name.

So if more columns are added in the future, the number of rows will increase exponentially which will slow down the model. I am sure you saw that for only a handful of rows of sales data, the new model has created around 400 rows.

Second is that, there are multiple rows being created for the same combination of sales, which is completely wrong.

As you can see for 1st Feb 2018, Product 2, in USA for B 3 rows have been created, which will give a bloated up result.

For these reasons I am not in the favor of adding rows with 0 sales.

Please let me know if you can still come up with some other way to fix this.

Thank you for all your help.

Vishesh Jain

Hi mail2vjj

Hi Vishesh,

I suggest creating a new post with this problem.....

I hope majority of your problems have been resolved...only this part is giving trouble

Wish you all the best