Dynamic classification basing on percentiles

[TzuChiao26]

Hello Fabric Community,

I am trying to perform the BCG "Share Growth Matrix" classification on my dataset (What Is the Growth Share Matrix? | BCG). For example, I want to classify customers basing on their [Volume CP] and [Volume Δ] like this:

Growth Share Classification =
SWITCH(
    TRUE(),
    [Volume CP] >= [Volume CP PR67] && [Volume Δ] >= [Volume Δ PR67], "Star",
    [Volume CP] <  [Volume CP PR67] && [Volume Δ] >= [Volume Δ PR67], "Question Mark",
    [Volume CP] >= [Volume CP PR67] && [Volume Δ] <  [Volume Δ PR67], "Cash Cow",
    [Volume CP] <  [Volume CP PR67] && [Volume Δ] <  [Volume Δ PR67], "Dog",
    BLANK()
)

Where "PR67" means a percentile ranking above 67% among customers. (Detailed dataset description in the next paragraph)
I want the value of PR67 subject to slicers. For example, when user select a particular Sales Team, the value should reflect only customer orders from that team.


For my dataset, each row is an invoice line, with the following relevant fields:

Date, Sales Team, Volume, Item, Customer.

To define "growth", I've implemented parameters that let the user choose which periods to compare, and came up with 2 measures:
Volume CP (Total volume for the current period) and Volume LP (Total volume for the last period)

Volume CP =
VAR YearParam = 'Current Year'[Current Year Value]
VAR MonthParam = 'Current Month'[Current Month Value]
VAR DayParam = 'Current Day'[Current Day Value]
VAR FirstOfMonth = DATE(YearParam, MonthParam, 1)
VAR LastOfMonth = EOMONTH(FirstOfMonth, 0)
VAR LastDayOfMonth = DAY(LastOfMonth)
VAR SafeDay = 
    IF(DayParam > LastDayOfMonth, LastDayOfMonth, DayParam)
VAR CurrentDate = DATE(YearParam, MonthParam, SafeDay)
VAR TimeRes = SELECTEDVALUE('Period Selection Table'[Time Resolution Table])

VAR CalcType = SELECTEDVALUE('Range Type Table'[Range Type]) -- "To Date" or "Rolling"
VAR MinRollingDate = 
    SWITCH(
        TimeRes,
        "Year", EDATE(CurrentDate, -12),
        "Quarter", EDATE(CurrentDate, -3),
        "Month", EDATE(CurrentDate, -1),
        BLANK()
    )
RETURN

SWITCH(
    TRUE(),
    -- Year To Date
    TimeRes = "Year" && CalcType = "To Date",
        CALCULATE(
            SUM(FCT_INVOICES[TONS_raw]),
            FILTER(
                FCT_INVOICES,
                YEAR(FCT_INVOICES[DATE]) = YearParam
            )
        ),

    -- Quarter To Date
    TimeRes = "Quarter" && CalcType = "To Date",
        CALCULATE(
            SUM(FCT_INVOICES[TONS_raw]),
            FILTER(
                FCT_INVOICES,
                YEAR(FCT_INVOICES[DATE]) = YEAR(CurrentDate) &&
                QUARTER(FCT_INVOICES[DATE]) = QUARTER(CurrentDate)
            )
        ),

    -- Month To Date
    TimeRes = "Month" && CalcType = "To Date",
        CALCULATE(
            SUM(FCT_INVOICES[TONS_raw]),
            FILTER(
                FCT_INVOICES,
                YEAR(FCT_INVOICES[DATE]) = YearParam &&
                MONTH(FCT_INVOICES[DATE]) = MonthParam
            )
        ),

    -- Rolling 
    CalcType = "Rolling",
        CALCULATE(
            SUM(FCT_INVOICES[TONS_raw]),
            FILTER(
                FCT_INVOICES,
                FCT_INVOICES[DATE] > MinRollingDate &&
                FCT_INVOICES[DATE] <= CurrentDate
            )
        ),
    BLANK()
)

Volume LP was constructed using a similar logic.

From these two measures, I can derive change of volume:

Volume Δ = [Volume CP] - [Volume LP]

 And I am trying to obtain [Volume CP PR67] and [Volume Δ PR67] by customer. Simply put, when I have a table like this:

Customer	Volume CP	Volume Δ
Apple	5	-1
Google	3	0
Amazon	12	5
...	...	...

 I need PR67 from column 2 and 3.

When the user changes slicer to look at a specific Sales Team, the above table would change accordingly, and so should the PR67 values. Same is true when the user changes what periods they would like to inspect.

[v-saisrao-msft]

Hi @TzuChiao26,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-saisrao-msft · Accepted solution]

Hi @TzuChiao26,

Thank you for reaching out to the Microsoft Fabric Forum Community.

You're trying to classify customers into BCG Growth Share Matrix categories (e.g., Star, Dog, Cash Cow, Question Mark) in Power BI using measures like Volume cp and VolumeΔ (Change), below is the .PBIX file attached for your reference.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you.