Classification count by Product Range

[RajK2]

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

[v-karpurapud · Accepted solution]

Hi @RajK2 

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.

[RajK2]

@v-karpurapud 

fantastic and really support me to sort out the solution.

steps you explained in DAX and really amazing .. awesome, Thank you

[v-karpurapud]

Hi @RajK2 

I'm happy it was useful for you. If you have any questions,please feel free to reach out.


Regards, 
Karpurapu D.

[Elena_Kalina]

Hi @RajK2 

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.

[lbendlin]

Sounds like you want a Pareto.  There are built-in functions in DAX like PERCENTILEX  that can get you there.  Have you tried these?