Dynamic classification basing on percentiles

Question

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(&nbsp; &nbsp; TRUE(),&nbsp; &nbsp; [Volume CP] &gt;= [Volume CP PR67] &amp;&amp; [Volume Δ] &gt;= [Volume Δ PR67], "Star",&nbsp; &nbsp; [Volume CP] &lt; &nbsp;[Volume CP PR67] &amp;&amp; [Volume Δ] &gt;= [Volume Δ PR67], "Question Mark",&nbsp; &nbsp; [Volume CP] &gt;= [Volume CP PR67] &amp;&amp; [Volume Δ] &lt; &nbsp;[Volume Δ PR67], "Cash Cow",&nbsp; &nbsp; [Volume CP] &lt; &nbsp;[Volume CP PR67] &amp;&amp; [Volume Δ] &lt; &nbsp;[Volume Δ PR67], "Dog",&nbsp; &nbsp; 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 = &nbsp; &nbsp; IF(DayParam &gt; 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 = &nbsp; &nbsp; SWITCH(&nbsp; &nbsp; &nbsp; &nbsp; TimeRes,&nbsp; &nbsp; &nbsp; &nbsp; "Year", EDATE(CurrentDate, -12),&nbsp; &nbsp; &nbsp; &nbsp; "Quarter", EDATE(CurrentDate, -3),&nbsp; &nbsp; &nbsp; &nbsp; "Month", EDATE(CurrentDate, -1),&nbsp; &nbsp; &nbsp; &nbsp; BLANK()&nbsp; &nbsp; )RETURNSWITCH(&nbsp; &nbsp; TRUE(),&nbsp; &nbsp; -- Year To Date&nbsp; &nbsp; TimeRes = "Year" &amp;&amp; CalcType = "To Date",&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUM(FCT_INVOICES[TONS_raw]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FCT_INVOICES,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; YEAR(FCT_INVOICES[DATE]) = YearParam&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; -- Quarter To Date&nbsp; &nbsp; TimeRes = "Quarter" &amp;&amp; CalcType = "To Date",&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUM(FCT_INVOICES[TONS_raw]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FCT_INVOICES,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; YEAR(FCT_INVOICES[DATE]) = YEAR(CurrentDate) &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; QUARTER(FCT_INVOICES[DATE]) = QUARTER(CurrentDate)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; -- Month To Date&nbsp; &nbsp; TimeRes = "Month" &amp;&amp; CalcType = "To Date",&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUM(FCT_INVOICES[TONS_raw]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FCT_INVOICES,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; YEAR(FCT_INVOICES[DATE]) = YearParam &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MONTH(FCT_INVOICES[DATE]) = MonthParam&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; -- Rolling &nbsp; &nbsp; CalcType = "Rolling",&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUM(FCT_INVOICES[TONS_raw]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FCT_INVOICES,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FCT_INVOICES[DATE] &gt; MinRollingDate &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FCT_INVOICES[DATE] &lt;= CurrentDate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; BLANK())Volume LP was constructed using a similar logic.&nbsp;From these two measures, I can derive change of volume:Volume Δ = [Volume CP] - [Volume LP]&nbsp;And I am trying to obtain [Volume CP PR67] and&nbsp;[Volume Δ PR67] by customer. Simply put, when I have a table like this:CustomerVolume CPVolume Δ Apple5-1Google30Amazon125.........&nbsp;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.&nbsp;

v-saisrao-msft · Accepted Answer

Hi&nbsp;TzuChiao26,
Thank you for reaching out to the Microsoft Fabric Forum Community.
&nbsp;
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.
&nbsp;
If this post&nbsp;helps, then please give us ‘Kudos’ and consider&nbsp;Accept it as a solution&nbsp;to help the other members find it more quickly.
&nbsp;
Thank you.

Forum Discussion

Dynamic classification basing on percentiles

2 Replies

Related content

Fabric + Purview (Data Classification)

Dynamic calculation of percentiles

Classification from dynamic data

Dynamic classification by measure

Dynamic Classification with two criteria

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