Dynamic Classification and Filtering in Power BI
Context :
- Power BI allows easy data exploration.
- Normal slicers/filters are static >>values don’t change.
- Sometimes, classification needs to be dynamic based on applied filters.
Example Scenario :
- A company has many products across regions/outlets.
- Analysts want to identify top-performing vs lagging products.
- Performance changes depending on region/time.
- Goal >> dynamically classify products and filter by these categories.
Pareto Chart & Principle :
- Pareto Principle (80/20 rule): 80% results come.
- In sales >>80% sales from 20% of products.
- Use Pareto chart:
- Bars = product sales (sorted highest to lowest).
- Line = cumulative % of sales.
- Dynamic colouring = key focus.
Step 1: Base Measure :
- Start with Total Sales Amount:
Total Sales Amount = SUM ( 'Sales'[Sales Amount] )
Step 2: Product Ranking :
Product Rank =
RANKX ( CALCULATETABLE ( 'Product', ALL ( 'Product'[Product] ) ),
[Total Sale Amount]
)
Step 3: Cumulative Sales :
- Calculate cumulative sales by rank:
Cumulative Total Sales Amount =
VAR _rank = CALCULATE ( [Product Rank] )
RETURN
CALCULATE (
[Total Sale Amount],
FILTER (
CALCULATETABLE ( VALUES ( 'Product'[Product] ), ALL ( 'Product'[Product] ) ),
[Product Rank] <= _rank
)
)
Step 4: Percentage of Total :
- Convert cumulative into % of total:
Percent of Total Sales =
VAR _totalSales = CALCULATE ( [Total Sale Amount], ALL ( 'Product'[Product] ) )
VAR _cumulative = [Cumulative Total Sales Amount]
RETURN DIVIDE ( _cumulative, _totalSales )
Step 5: Classification :
- Categorize products dynamically:
- High >> Top 50% of sales
- Medium >> Next 30% (up to 80%)
- Low >> Remaining 20%
Product Category =
VAR _rankPct = [Percent of Total Sales]
RETURN
SWITCH ( TRUE (),
_rankPct <= 0.5, "High",
_rankPct <= 0.8, "Medium",
"Low"
)
Classification Filtering :
- Create Category Table >> High, Medium, Low (for slicer).
- Measure to get selected category:
Selected Category = SELECTEDVALUE ( 'Product Categories'[Category], "ALL" )
- Check if product belongs in selected category:
Product in Category =
VAR _selected = [Selected Category]
VAR _category = [Product Category]
RETURN IF ( OR ( _category = _selected, _selected = "ALL" ), 1, 0 )
Building the Pareto Plot :
- Use Column & Line Chart :
- X-axis >> Product
- Column (Y1) >> Total Sales Amount
- Line (Y2) >> Percent of Total Sales
Colouring :
Product Category Num =
VAR _category = [Product Category]
RETURN SWITCH ( _category,
"High", 1,
"Medium", 2,
"Low", 3
)
Filtering :
- Add Product in Category as visual filter >> keep only = 1.
Outcome :
- Final report shows:
- Full Pareto chart.
- Category-based versions (High / Medium / Low filtered).
Insights :
- Identifies products contributing most to sales.
- Helps focus on top products or under performing ones.
- Supports better decision-making.
Related
