topic Sum rooms only if Occ is a certain percentage in DAX Commands and Tips

Sum rooms only if Occ is a certain percentage

BrittLeigh14608 — Fri, 23 Dec 2022 22:31:13 GMT

Hello!

I am struggling trying to figure out how to calculate the sum of room nights only if our Occ% is >=95%. I have tried everything I can think of and don't know how to search for what I am looking for.

Re: Sum rooms only if Occ is a certain percentage

Greg_Deckler — Fri, 23 Dec 2022 23:34:04 GMT

BrittLeigh14608 If Occ% is a measure then you will need to do a measure aggregation across the next level of the hierarchy that also includes a filter. It's a little hard to see what is going on here.

This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

You could include a FILTER in your SUMMARIZE for example.

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Sum rooms only if Occ is a certain percentage

BrittLeigh14608 — Sun, 25 Dec 2022 23:53:19 GMT

Thank you for getting back with me so quickly. I read the blog posts and I can't figure it out. (Sorry for the delay on getting back. We entered Icemageddon and I lost power.)

Here are my Measures:

And here is a list of the remaining data.

Re: Sum rooms only if Occ is a certain percentage

BrittLeigh14608 — Sun, 25 Dec 2022 23:58:37 GMT

I think my main problem is that each reservation tracks individually by each individual stay date.