How to Create Percentile Groupings in PowerBI

[marksaba]

Hello-

Can someone please show me how to create percentile groupings in PowerBI that can be adjusted based on filters I apply? See below image for reference. I manually Pivoted out Items and Invoice Sales to group the percentiles but I need this to be able to be done automatically.

For instance, when I filter to a Category, I would like to see:

Rows: Top Ten Percent (by Sales), Next 20%, Next 20%, Next 20%, and Bottom 10%
Values: Count of Items, Sales, etc

Keep in mind, I want the percentile groupings to be variable. If I filter to Category X, I would like to know how many items make up the Top Ten % of that Category.

Thanks in advance!

[v-yanjiang-msft]

Hi @marksaba ,

Has @OwenAuger 's solution solved your problem? If yes, please consider accept it as the solution to help the other members find it more quickly.

Best Regards,
Community Support Team _ kalyj

[OwenAuger]

Hi @marksaba 

What you have described is pretty similar to the ABC classification pattern.

https://www.daxpatterns.com/abc-classification/

Take a look at the Dynamic ABC Classification section.

You would just need to define the equivalent of the ABC Classes table with 6 rows, corresponding to the segmentation you have shown above.

Otherwise, the ABC Sales Amount measure from DAX Patterns should be pretty well re-usable, with appropriate table/column renamings.

Feel free to post back with your table/column names and a data model diagram, or some sample data, and I could take a further look.

Kind regards,

Owen

[marksaba]

Thank you for the help @OwenAuger ! This seems to be what I need. I'm having a hard-time figuring out the DAX code however. Could you assist in typing up in the code with the following information?

Table name: "DATA"

Sales column name: "Invoice Sales"

Product column name: "Material Code"

Thanks in advance!

[OwenAuger]

Hi again @marksaba 

No worries 🙂
I thought it would help to set up a sample PBIX which I've done using Contoso data (attached).

I followed the DAX Patterns ABC Classiciation pattern pretty much exactly.

I would suggest that it is best to have a Product dimension table.

I have created two versions of the measures though to illustrate:

• v1 using DATA[Material Code]
• v2 using the 'Product' table which is related to DATA on the Material Code column

This is the Percentiles table, which is the equivalent of the ABC Classification table from DAX Patterns.

Percentile & Percentile Alternate are set to sort by Percentile Index.

The Sales Amount measure is defined as:

Sales Amount = 
SUM ( DATA[Invoice Sales] )

And these are the two versions of the Percentile Sales Amount measure:

Percentile Sales Amount v1 = 
VAR SalesByProduct =
    ADDCOLUMNS (
        ALLSELECTED ( DATA[Material Code] ),
        "@ProdSales", [Sales Amount]
    )
VAR AllSales =
    CALCULATE (
        [Sales Amount],
        ALLSELECTED ( DATA[Material Code] )
    )
VAR CumulatedPctByProduct =
    ADDCOLUMNS (
        SalesByProduct,
        "@CumulatedPct",
        VAR CurrentSalesAmt = [@ProdSales]
        VAR CumulatedSales =
            FILTER (
                SalesByProduct,
                [@ProdSales] >= CurrentSalesAmt
            )
        VAR CumulatedSalesAmount =
            SUMX (
                CumulatedSales,
                [@ProdSales]
            )
        RETURN
            DIVIDE (
                CumulatedSalesAmount,
                AllSales
            )
    )
VAR ProductsInClass =
    FILTER (
        CROSSJOIN (
            CumulatedPctByProduct,
            Percentiles
        ),
        AND (
            [@CumulatedPct] > Percentiles[Lower Boundary],
            [@CumulatedPct] <= Percentiles[Upper Boundary]
        )
    )
VAR Result =
    CALCULATE (
        [Sales Amount],
        KEEPFILTERS ( ProductsInClass )
    )
RETURN
    Result

Percentile Sales Amount v2 = 
VAR SalesByProduct =
    ADDCOLUMNS (
        ALLSELECTED ( 'Product' ),
        "@ProdSales", [Sales Amount]
    )
VAR AllSales =
    CALCULATE (
        [Sales Amount],
        ALLSELECTED ( 'Product' )
    )
VAR CumulatedPctByProduct =
    ADDCOLUMNS (
        SalesByProduct,
        "@CumulatedPct",
        VAR CurrentSalesAmt = [@ProdSales]
        VAR CumulatedSales =
            FILTER (
                SalesByProduct,
                [@ProdSales] >= CurrentSalesAmt
            )
        VAR CumulatedSalesAmount =
            SUMX (
                CumulatedSales,
                [@ProdSales]
            )
        RETURN
            DIVIDE (
                CumulatedSalesAmount,
                AllSales
            )
    )
VAR ProductsInClass =
    FILTER (
        CROSSJOIN (
            CumulatedPctByProduct,
            Percentiles
        ),
        AND (
            [@CumulatedPct] > Percentiles[Lower Boundary],
            [@CumulatedPct] <= Percentiles[Upper Boundary]
        )
    )
VAR Result =
    CALCULATE (
        [Sales Amount],
        KEEPFILTERS ( ProductsInClass )
    )
RETURN
    Result

There are similar measures for counting SKUs.

Regards,

Owen