cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Percentile of a Group?

Hello Everyone:

in a nutshell, I have a table that has a Key, a total revenue value and dates.  I'm trying to calculate the 25, 50 and 75 percentiles and conditionally format if group is in each one.  When I add values to a Table or matrix, the total for sales, the max of date, counts all work correctly. But I've tried percentilex and percentileinc and the problem is, it's breaking down at the row level. So let's say revenue per individual spans from 1-$1000.  Some customers spend like 100k. I want to look at the percentiles at the group level. So looking at the image below, I'd like to use Percentile (or Max * 75, 50, 25) on the All Room Revenue group.  I feel like I need an All somewhere or an AllExcept(reservationkey) but I can't seem to get that to work.
None of these work, as it seems to calcluate the percentage at the individual record level instead of group

RFM Monetary Quartile 3 = CALCULATE(PERCENTILEX.INC(Visits, [Room_Revenue], .50), ALLEXCEPT(Visits, Visits[ReservationKey]))
RFM Monetary Quartile 3 = PERCENTILE.INC(Visits[Room_Revenue], .50)



RFMSampleImage.png

1 ACCEPTED SOLUTION

@W_G_Ryan 

Let's try to first deal with the revenue, just to make sure I understand properly what you need and then we can figure out the rest.

 

1. Create a measure that returns the total revenue per reservation. This is what you were already doing with an explicit measure by placing the [room_revenue] column in the table visual and choosing the Sum aggregation. Most of the time it's better to use implicit measures. You have more flexibility and they can be used later (as we do in this case) 

 

Revenue by reservation = SUM('Room Transactions'[room_revenue]) 

 

2. Create a measure that returns the maximum revenue of all reservations:

 

Maximum of sales by Reservation = 
MAXX (
    ALL ( 'Room Transactions'[KeyValue] ),
    [Revenue by reservation]
)

 

3. Create a measure to determine the revenue band for each reservation. I've called it band because what you describe (if i understood correctly) is, strictly speaking, not a percentile. Anyway, this is a minor detail. 

 

RevenueBand =
VAR maxSales_ = [Maximum of sales by Reservation]
VAR revCurrentReservation_ = [Revenue by reservation]
RETURN
    SWITCH (
        TRUE (),
        revCurrentReservation_ < maxSales_ * 0.25, "0-25",
        revCurrentReservation_ < maxSales_ * 0.50, "25-50",
        revCurrentReservation_ < maxSales_ * 0.75, "50-75",
        "75-100"
    )

 

This last measure uses the two previous ones and shows what revenue band each reservation is in. I've chosen to set the output as text for illustration purposes but that can be changed easily if required. 

4. Place all three measures in the visual table (or any subset of them)

5. See it all at work in the attached file.

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

 

View solution in original post

3 REPLIES 3
Super User III
Super User III

Hi@W_G_Ryan

Can you share the pbix or a pbix with dummy data that reproduces the issue?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

 

 

I attached a PBI sample notebook and file.  If you look at the example, i had to strip data out so it's not exact same thing, but it shows what I'm trying to do.  The All Sales Amount (should be named Max Sales Amount) should be the Maximum of sales by Reservation.  Instead it's  using a value of 984. when what I was looking for was 8664.  I'm trying to create percentiles after that but when I do Max * .75 it shows on the base record amounts not the grouped record amounts.  Basically, I want to look at the Room Revenue by customer, see if the total amount they spend is above .75, .50, .25 percentiles (and I need to obviously make sure I calculate those right).  Then do the same on Counts (I can get counts for each by using COUNTROWS) and then the same for DaysSinceLastStay (which isn't on there, but it's basically Latest Visit Date - today with a datediff.  I'm just trying to create percentiles in each of those categories.  Does that make sense?  Really appreciate you helping and I think you DM'd me before about help, I'm likely to take you up on that.  Anyway, if you see anything obvious I'm missing, I'd appreciate it.

https://1drv.ms/u/s!Ag_Sg7lSA4PIgeJU0DUBzcALNjsFzg?e=0lQkHs

@W_G_Ryan 

Let's try to first deal with the revenue, just to make sure I understand properly what you need and then we can figure out the rest.

 

1. Create a measure that returns the total revenue per reservation. This is what you were already doing with an explicit measure by placing the [room_revenue] column in the table visual and choosing the Sum aggregation. Most of the time it's better to use implicit measures. You have more flexibility and they can be used later (as we do in this case) 

 

Revenue by reservation = SUM('Room Transactions'[room_revenue]) 

 

2. Create a measure that returns the maximum revenue of all reservations:

 

Maximum of sales by Reservation = 
MAXX (
    ALL ( 'Room Transactions'[KeyValue] ),
    [Revenue by reservation]
)

 

3. Create a measure to determine the revenue band for each reservation. I've called it band because what you describe (if i understood correctly) is, strictly speaking, not a percentile. Anyway, this is a minor detail. 

 

RevenueBand =
VAR maxSales_ = [Maximum of sales by Reservation]
VAR revCurrentReservation_ = [Revenue by reservation]
RETURN
    SWITCH (
        TRUE (),
        revCurrentReservation_ < maxSales_ * 0.25, "0-25",
        revCurrentReservation_ < maxSales_ * 0.50, "25-50",
        revCurrentReservation_ < maxSales_ * 0.75, "50-75",
        "75-100"
    )

 

This last measure uses the two previous ones and shows what revenue band each reservation is in. I've chosen to set the output as text for illustration purposes but that can be changed easily if required. 

4. Place all three measures in the visual table (or any subset of them)

5. See it all at work in the attached file.

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

 

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.