Forum Discussion

homeshagarwal's avatar
homeshagarwal
Regular Visitor
2 years ago

Calculating Percentiles vs. Percentiles using Visual Reference Lines

Hi all!
I created a column chart displaying customers' sales and added 2 reference lines- average ($5.63 M) and 80th percentile line ($7.91 M).

Now, I wish to calculate the same percentile value using DAX, I'm getting $731.62M!

Sales Over 80Percentile = CALCULATE(PERCENTILEX.INC(Sales,SUM(Sales[Gross Sales]),.8), ALL(Customers[Customer_ID]))


Could you please guide me on how I can get the same results using DAX??

1 Reply

  • OwenAuger's avatar
    OwenAuger
    Super User
    2 years ago

    Hi homeshagarwal 

    One issue with your measure is that SUM ( Sales[Gross Sales] ) needs to be wrapped in CALCULATE (or define a measure and reference that measure instead).

    Also, to replicate the behaviour of the percentile line, we have to use ALLSELECTED and remove blanks to ensure the Customers included in the visual are included.

     

    Something like:

     

     

    Sales Over 80Percentile =
VAR CustomerSales =
    FILTER (
        ADDCOLUMNS (
            ALLSELECTED ( Customers[Customer_ID] ),
            "@Sales", CALCULATE ( SUM ( Sales[Gross Sales] ) )
        ),
        NOT ISBLANK ( [@Sales] )
    )
RETURN
    PERCENTILEX.INC ( CustomerSales, [@Sales], 0.8 )

     

     

    Does this work for you?

     

    Regards