Forum Discussion

Sirhawk3017's avatar
Sirhawk3017
Icon for Helper II rankHelper II
1 year ago

Quartiles based on YTD Sales Measure

Not sure how to do this but I want to create quartiles based on the YTD sales measure. Here's the PowerBI file and heres an excel of what I'm trying to do. Column I in Excel is what I'd like the outc...
General Comment
Need Help
Show and Tell
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi Sirhawk3017 ,

 

In DAX, you can use the PERCENTILE.INC function to replace the QUARTILE.INC function in Excel. The PERCENTILE.INC function calculates the value at a specified percentile in a dataset. For the first quartile (i.e., the 25th percentile), you can write:

FirstQuartile = PERCENTILE.INC('Table'[Column], 0.25)

In your report, please try

Quartile = 
VAR SalesValue = [FM Sales YTD]
VAR Quartile1 = PERCENTILE.INC('FM_Sales'[Total Sales ($)], 0.25)
VAR Quartile2 = PERCENTILE.INC('FM_Sales'[Total Sales ($)], 0.50)
VAR Quartile3 = PERCENTILE.INC('FM_Sales'[Total Sales ($)], 0.75)
RETURN 
    SWITCH(
        TRUE(),
        SalesValue <= Quartile1, "Q1",
        SalesValue <= Quartile2, "Q2",
        SalesValue <= Quartile3, "Q3",
        "Q4"
    )

Best Regards,
Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly