Forum Discussion
Classification count by Product Range
Hi
I need DAX Formula product count based on sales amount criteria, Total Sales 6,069,725 * 70% , 20%, 10% for AIR
example, total product for AIR = 18 then
70% sales by product count = 10,
20 % sales by product count = 5,
10 % sales by product count = 3 however total product AIR is 18 we can't calculate extact sales % to get count as approx is fine.
result - if we create table Type and Class and drill down vehicle type, class it will show next level
| TYPE | CLASS |
| AIR | |
| EXCELLENT | 10 |
| V GOOD | 5 |
| GOOD | 3 |
| CIRCLE | |
| EXCELLENT | --- |
| V GOOD | --- |
| GOOD | --- |
TABLE Sample :
| YYYY-QQ | Type | VEHICLE TYPE | Product | Sales |
| 2025-Q3 | AIR | BUS | URO | 2316367 |
| 2025-Q3 | AIR | BUS | FLUID | 810661 |
| 2025-Q3 | AIR | BUS | MODULAR | 806755 |
| 2025-Q3 | AIR | BUS | STAR DRIVE | 598993 |
| 2025-Q3 | AIR | BUS | VOLVO DING | 297975 |
| 2025-Q3 | AIR | AUTO | STARKE | 284632 |
| 2025-Q3 | AIR | BUS | BRAKE | 265213 |
| 2025-Q3 | AIR | BUS | ATE BRAKE | 176020 |
| 2025-Q3 | AIR | AUTO | VALUE | 160033 |
| 2025-Q3 | AIR | AUTO | MYCLE | 74204 |
| 2025-Q3 | AIR | AUTO | AXLE | 72967 |
| 2025-Q3 | AIR | AUTO | CROSSMEMBER | 71564 |
| 2025-Q3 | AIR | BUS | ABE BRAKE | 56314 |
| 2025-Q3 | AIR | BUS | SYSTEM DUALBUS | 47852 |
| 2025-Q3 | AIR | BUS | LITTAL | 15132 |
| 2025-Q3 | AIR | AUTO | TIMING BELT | 7649 |
| 2025-Q3 | AIR | AUTO | VALEO | 7394 |
| 2025-Q3 | AIR | AUTO | DASHBOARD STEREO | 0 |
| 2025-Q3 | CIRCLE | CAR | SWIFT SERIES | 2969589 |
| 2025-Q3 | CIRCLE | CAR | SERIES PART | 946618 |
| 2025-Q3 | CIRCLE | CAR | OEC | 583680 |
| 2025-Q3 | CIRCLE | CAR | HORN PART | 548844 |
| 2025-Q3 | CIRCLE | CAR | TABLE PART | 323616 |
| 2025-Q3 | CIRCLE | CAR | ELITE STONG | 48748 |
Hi Anonymous
Thank you for reaching out to the Microsoft Fabric community. And thank you lbendlin and Elena_Kalina for sharing helpful insights.
We have implement a combination of calculated columns and a measure within the dataset. The classification logic categorizes products into EXCELLENT, V GOOD, and GOOD tiers based on their contribution to total sales, with thresholds at 70%, 90%, and 100% respectively.
--------Measures------- TotalSalesPerType = CALCULATE( SUM('SalesData'[Sales]), ALLEXCEPT('SalesData', 'SalesData'[Type]) ) ----------------- Product Count = DISTINCTCOUNT('SalesData'[Product]) ---Calculated Columns--- CumulativeSales = CALCULATE( SUM('SalesData'[Sales]), FILTER( 'SalesData', 'SalesData'[Type] = EARLIER('SalesData'[Type]) && 'SalesData'[Sales] >= EARLIER('SalesData'[Sales]) ) ) ------------------------------ CumulativePercent = DIVIDE('SalesData'[CumulativeSales], [TotalSalesPerType]) ------------------------------ Classification = SWITCH( TRUE(), 'SalesData'[CumulativePercent] <= 0.7, "EXCELLENT", 'SalesData'[CumulativePercent] <= 0.9, "V GOOD", "GOOD" )
Please refer to the attached .pbix file for a working example and review the implementation.
I hope this information proves helpful. If not, please feel free to share additional details, and we will be happy to assist you further.
Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.
5 Replies
Sounds like you want a Pareto. There are built-in functions in DAX like PERCENTILEX that can get you there. Have you tried these?
Hi Anonymous
Cteate a calculate column
Tier Simplified = VAR CurrentType = 'Table'[Type] VAR CurrentProductSales = 'Table'[Sales] // 1. Get total sales for the type VAR TotalSalesByType = CALCULATE( SUM('Table'[Sales]), FILTER( ALL('Table'), 'Table'[Type] = CurrentType ) ) // 2. Determine product's contribution percentage VAR SalesShare = CurrentProductSales / TotalSalesByType // 3. Categorize (adjust thresholds as needed) RETURN SWITCH( TRUE(), SalesShare >= 0.05, "EXCELLENT", // Top products (70% of sales) SalesShare >= 0.01, "V GOOD", // Medium products (20% of sales) "GOOD" // Others (10% of sales) )
You can then rename this value as "Class" in the visual
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thank you.
Hi Anonymous
Thank you for reaching out to the Microsoft Fabric community. And thank you lbendlin and Elena_Kalina for sharing helpful insights.
We have implement a combination of calculated columns and a measure within the dataset. The classification logic categorizes products into EXCELLENT, V GOOD, and GOOD tiers based on their contribution to total sales, with thresholds at 70%, 90%, and 100% respectively.
--------Measures------- TotalSalesPerType = CALCULATE( SUM('SalesData'[Sales]), ALLEXCEPT('SalesData', 'SalesData'[Type]) ) ----------------- Product Count = DISTINCTCOUNT('SalesData'[Product]) ---Calculated Columns--- CumulativeSales = CALCULATE( SUM('SalesData'[Sales]), FILTER( 'SalesData', 'SalesData'[Type] = EARLIER('SalesData'[Type]) && 'SalesData'[Sales] >= EARLIER('SalesData'[Sales]) ) ) ------------------------------ CumulativePercent = DIVIDE('SalesData'[CumulativeSales], [TotalSalesPerType]) ------------------------------ Classification = SWITCH( TRUE(), 'SalesData'[CumulativePercent] <= 0.7, "EXCELLENT", 'SalesData'[CumulativePercent] <= 0.9, "V GOOD", "GOOD" )
Please refer to the attached .pbix file for a working example and review the implementation.
I hope this information proves helpful. If not, please feel free to share additional details, and we will be happy to assist you further.
Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.fantastic and really support me to sort out the solution.
steps you explained in DAX and really amazing .. awesome, Thank you
Hi Anonymous
I'm happy it was useful for you. If you have any questions,please feel free to reach out.
Regards,
Karpurapu D.
