Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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 % ...
Need Help
  • v-karpurapud's avatar
    v-karpurapud
    1 year ago

    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.

Elena_Kalina's avatar
Elena_Kalina
Icon for Solution Sage rankSolution Sage
1 year ago

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.