topic Re: WoW % on mutilple level in DAX Commands and Tips

WoW % on mutilple level

PouxieDouxie — Fri, 25 Aug 2023 19:19:36 GMT

Currently, my formula gives me the % of the total regardless of the number of levels that I indicate (by Pillar, by type of incident etc). How to add levels in my calculation. Instead of seeing the % on the total like in my following table, I would like the % to be in relation to the number that appears on the line. Is this possible with DAX? All examples that I had found are for $ and there is only one level involved.

Incidents This Week = CALCULATE(DISTINCTCOUNT('Incidents_Merge'[Number]),FILTER(ALL('Incidents_Merge'),'Incidents_Merge'[WeekRank]=MAX('Incidents_Merge'[WeekRank])))

Incidents Last Week = CALCULATE(DISTINCTCOUNT('Incidents_Merge'[Number]),FILTER(ALL('Incidents_Merge'),'Incidents_Merge'[WeekRank]=MAX('Incidents_Merge'[WeekRank])-1))

Incidents WOW % = ([Incidents This Week]-[Incidents Last Week])/[Incidents Last Week]

Don't hesitate if you have any question

Thank you

Re: WoW % on mutilple level

lbendlin — Sun, 27 Aug 2023 22:48:57 GMT

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: WoW % on mutilple level

PouxieDouxie — Mon, 04 Sep 2023 17:49:50 GMT

Hello, thank you very much for your answer. Sorry for the delay, I was not available. It is very difficult to provide sample data as almost all my data is sensitive data. One of the solution is to build everything from scratch but it will be very time-consuming. Instead, I will try to explain in details what is the issue that I'm facing.

In the screenshot provided originally, there is a "Pillar" column and a "Type" column.

My percentage are based on "Total" row values and I want to be able to have percentage based on Pillar/Type for the WoW column.

If you look at the "WoW" column for July 9th, all the values are equal to 28.87 (percentage value based on total, 111).

I want to have the percentage for the Pillar X and the Type "P3", Pillar X and Type "P4", pillar Y and Type "P3", pillar Y and Type "P4", etc.

It seems that if I have only one level, it works but if I have several levels (a hierarchy of sub-levels, like Pillar, Type...), my formula are not suitable.

So, my question is now more theoric: how to expand any WoW formula in more than one level ? Is there a way in DAX to add a level ? What if I want to add several others sub-levels ?

I can give the WoW formula that I used (see original question) but after reflexion, in my opinion, the formula is maybe not so important, the mecanics to propagate the formula to sublevels is more the questionning that I have.