My current formula below allowed me to calculate the Percentiles for the values that I have. However, it took into account the entire range of values & rank within my table and not just within their own categories. So instead of creating percentiles within A/B/C separately, it created percentiles encompassing all of A/B/C.
How should I fix this so the percentiles are calculated within their own categories?
It would help to know what your data columns are, but basically you need to change the denominator of your divide function.
Using https://www.daxformatter.com/ to format your current formula you can see better where the denominator starts and that I have updated the ALL() to only remove filters on the current category, not the whole Z table.
@dragonus , In last step where you calculate denominator, you should use function AllExcept to group values by category. If you want to calculate denominator by group use AllExcept(table, category). Example when to use it: you have values by month and you would like to find percentage of each month in year total. Numerator = sum(amount) -- monthly Denumerator = calculate([Numerator], AllExcept(table, year)) -- yearly % of year = divide ([Numerator],[Denumerator])
I've tried using ALLEXCEPT, but the end result returned me numbers that are from the 0.000s to the thousands.... Not too sure why is this so... previously my percentiles are all in the 0.0 to 0.9s so I could use it to calculate appropriate categories for them later on, which is what I wanted