How to Create Percentile Groupings in PowerBI

Hi again Anonymous 

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