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 us...
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