Percentile Sub-total vs Total Issues

[JDLee23]

Hi,

I've encountered a complex problem, and would appreciate any insight anyone can give!

Basically, I'm trying to calculate the difference between the profit a store makes, and the value of the kth percentile of all stores across the country, with the kth percentile being a selectable variable. However, this needs to work across different sections (e.g. Clothes, Shoes, Household Items etc...), with the profit worked out individually for each section, and then totalled.

However, my current DAX formulas, shown below, seem to give a total as the profit difference between the store as a whole to the kth percentile of all stores in the country, e.g. it works out the total profit percentile, then compares to the total of the store, not for each individual section/category.

This measure works correctly on a visual which breaks it down by section, and then totals (e.g. the waterfall chart).

I guess it needs some GROUPBY style function, but I am not experienced enough with DAX to do this correctly - any advice will be much appreciated!

Current formula(s):

VAR CurrentPercentile =
    IF (
        HASONEVALUE ( 'Percentile'[Percent] ),
        LASTNONBLANK ( 'Percentile'[Percent], 1 ),
        0
    )

VAR PercentileProfit
    CALCULATE (
        PERCENTILEX.INC (
            SUMMARIZE (
                'Sales'[Store],
                'Sales'[Section],
                "GroupedProfit", SUM ( 'Sales'[Profit] )
            ),
            [GroupedProfit],
            CurrentPercentile
        ),
        ALL ( 'Sales'[Store] ),
        ALL ( 'Sales'[Section] )
    )

VAR ProfitDifference = PercentileProfit - SUM('Sales'[Profit])

RETURN ProfitDifference

[v-ljerr-msft]

Hi @JDLee23,

Could you post your table structures with some sample data, and the expected result against the data? So that we can better assist on this issue. It's better to share a sample pbix file which can reproduce the issue. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards