Calculating Percentiles vs. Percentiles using Visual Reference Lines

[homeshagarwal]

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??

[OwenAuger]

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