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 outcome to be in BI. 

 

Excel:

https://www.dropbox.com/scl/fi/zdo0sxxovdk16zehinfhd/FM-Quartile-What-I-want-Power-BI-to-do.xlsx?rlkey=2a3mnrrzhivw59rmkdewttk1e&st=5trupir0&dl=0

 

PowerBI:

https://www.dropbox.com/scl/fi/v5z0hmd17rl8pdsc4wopg/Need-Help.pbix?rlkey=uiv1jo7apw5i4uabn1jod00cj&st=ujrp0xkj&dl=0

General Comment
Need Help
Show and Tell
Tutorial Requests

2 Replies

  • 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