Rolling 12 Months ,Top Quartile Products Sales for each Category (Sales wise 25% of Top Products)

[sajalde]

I am struggling with the below problem in the PowerBI DAX Queries. Can you please here to fix the DAX issue or fresh DAX?  Which Visualization will be the best view for the below?

Calculate Rolling 12 Months, Top Quartile Products Sales for each Category

Sales wise 25% of Top Products,

Sales wise 25% of Buton Products,

Median Quartile Products 

Date Set Fields : - | Sale Date | Category | Products | INR Amount |

Calculate Rolling 12 Months Sales:
Rolling 12 Months Sales =
CALCULATE(
SUM(Sales[Quantity]),
DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH)
)

Identify Top Quartile Sales Amount:

Top Quartile Sales Amount =
PERCENTILEX.INC(
ALL(Sales[Products]),
[Rolling 12 Months Sales],
0.75
)

Identify Top Quartile Products for Each Category:
Top Quartile Products =
VAR TopQuartileSales = [Top Quartile Sales Amount]
RETURN
CALCULATETABLE(
VALUES(Sales[Products]),
FILTER(
ALL(Sales[Products]),
CALCULATE(
SUM(Sales[Quantity]),
Sales[Products] = EARLIER(Sales[Products]),
DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH)
) >= TopQuartileSales
)
)

Calculate Quantity of Top Quartile Products for Each Category:
Top Quartile Products Quantity =
VAR TopQuartileProducts = [Top Quartile Products]
RETURN
CALCULATE(
SUM(Sales[Quantity]),
FILTER(
Sales,
Sales[Products] IN TopQuartileProducts
)
)

Fetching an Error Sales[Products] IN TopQuartileProducts

Sample Data in CSV

Date,Category,Products,Amount
2023-01-01,Electronics,Laptop,90000
2023-01-05,Clothing,T-Shirt,600
2023-01-10,Home,Chair,3000
2023-02-02,Electronics,Smartphone,50000
2023-02-15,Clothing,Jeans,2500
2023-02-20,Home,Table,4000
2023-03-03,Electronics,Headphones,3000
2023-03-07,Clothing,Dress,1600
2023-03-25,Home,Sofa,7000
2023-04-05,Electronics,TV,60000
2023-04-10,Clothing,Shoes,2000
2023-04-22,Home,Bed,8000
2023-05-01,Electronics,Smartwatch,15000
2023-05-08,Clothing,Skirt,1200
2023-05-20,Home,Dining Table,5000
2023-06-02,Electronics,Tablet,40000
2023-06-12,Clothing,Jacket,1800
2023-06-28,Home,Kitchenware,2500
2023-07-03,Electronics,Game Console,35000
2023-07-15,Clothing,Sweater,2200
2023-07-30,Home,Vacuum Cleaner,4000
2023-08-05,Electronics,Drone,10000
2023-08-18,Clothing,Scarf,800
2023-08-25,Home,Blender,1500
2023-09-01,Electronics,Speaker,3000
2023-09-10,Clothing,Gloves,500
2023-09-22,Home,Washing Machine,45000
2023-10-04,Electronics,Earphones,2000
2023-10-12,Clothing,Hat,300
2023-10-20,Home,Heater,3500
2023-11-02,Electronics,Router,12000
2023-11-08,Clothing,Belt,400
2023-11-18,Home,Microwave,3000
2023-12-01,Electronics,Monitor,20000
2023-12-10,Clothing,Socks,100
2023-12-25,Home,Air Conditioner,60000

[talespin · Accepted solution]

hi @sajalde 

I am not clear on one thing, there are many definitions of Percentile out there.

What I am not sure of is how to handle ties. 

https://drive.google.com/file/d/1GYOpqpSysQ04hdWLWmNeLcw-ApZg6dDS/view?usp=sharing

I am sharing this file and I have use three methods, "Top Quartile Product Sales3" seems to return accurate results.

Top Quartile Product Sales - Using count of values method. (You many need to change the logic).

Top Quartile Product Sales2 - Using Rank method (You many need to change the logic).

Top Quartile Product Sales3 - Using PowerBI PERCENTILEX.INC (This one looks accurate to me, Logic Used - Find the 75th Percentile and remove T12 Sales equal and below this number).

I am using the sample data which you shared, Since Quartile is based on Rolling12Mth sales so that is why it starts from 202312.

 -----------------------------------------------------------------------------------

SalesAmount = SUM(Test[Amount])

 -----------------------------------------------------------------------------------

Rolling 12Mth Sales =

VAR _FirstDate = CALCULATE( MIN('CALENDAR'[Date]), REMOVEFILTERS('CALENDAR'[YearMonth]) )

VAR _LastDate = MAX('CALENDAR'[Date])

VAR _Period = DATESINPERIOD('CALENDAR'[Date], _LastDate, -12, MONTH)

VAR _Rolling12Mth = CALCULATE( SUMX( VALUES('CALENDAR'[YearMonth]), [SalesAmount]), _Period)

RETURN IF( EOMONTH(_LastDate, -12) + 1 >= _FirstDate, _Rolling12Mth, BLANK())

 -----------------------------------------------------------------------------------

Top Quartile Product Sales3 =

VAR _LastDate = MAX('CALENDAR'[Date])

VAR _Period = DATESINPERIOD('CALENDAR'[Date], _LastDate, -12, MONTH)

VAR _T12Sales = [Rolling 12Mth Sales]

VAR _ProdCountBelow =

CALCULATE(

    PERCENTILEX.INC(

        SUMMARIZE(test, Test[Category], Test[Products]),

        [Rolling 12Mth Sales],

        .75

    ),

    REMOVEFILTERS(Test[Products]),

    _Period

)

RETURN IF(

            HASONEFILTER(Test[Products]),

            IF( _T12Sales > _ProdCountBelow, 1, 0),

            BLANK())

[sajalde]

Thank you so much, I shared sample Data, but I have live data which not able to share, It would be great and helpful if you rectified the DAX or provided the DAX that I can apply to my current dataset.

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[lbendlin]

There's no point in calculating Rolling 12 months  - you don't have enough data for that to be meaningful.

With better data I would start with the ribbon chart visual as it gives you the ranking for free.