Calculating Percentiles vs. Percentiles using Visual Reference Lines

homeshagarwal — Sun, 22 Oct 2023 09:14:36 GMT

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

Re: Calculating Percentiles vs. Percentiles using Visual Reference Lines

OwenAuger — Mon, 23 Oct 2023 03:34:56 GMT

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

topic Calculating Percentiles vs. Percentiles using Visual Reference Lines in DAX Commands and Tips

Calculating Percentiles vs. Percentiles using Visual Reference Lines

Re: Calculating Percentiles vs. Percentiles using Visual Reference Lines