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

Grouping to get by date for getting percentages

I am trying to get a totall occupancy for a group of locaions.  We have some locations that have two people per room and some that have one person per room.  Both people are in the database but they will have the same unit number.  The other zinger here is that the Max Occupancy changed at one locaton to a lower ammount.  Here is a sample of the data:

 

DateLocationIDMax occupancy DateLocationIDMax Occupancy 
9/29/2020Fargo101140 10/1/2020Moorhead502108 
9/30/2020Fargo102

140

 10/1/2020Moorhead503108 
10/1/2020Fargo300140      
9/29/2020Moorhead500115      
9/29/2020Moorhead500115      
9/29/2020Moorhead502

115

      
9/29/2020Moorhead503115      
10/1/2020Moorhead500108      
10/1/2020Moorhead500108      

 

So, I need to count the ID of 500 as 1 as we do not look at people but at units when looking at occupancy.  

Issue number 1. Numerator needs to be 1 count per unit per day.  

Issue number 2. The denominator changes October 1st.  So I want to use the old Max through the 30th and the new max going forward. 

 

Thanks for any help.

Peter

 

1 ACCEPTED SOLUTION

Hi @petermb72 ,

 

Please check the following measure.

Measure = CALCULATE(DISTINCTCOUNT('Table'[ID]),ALLEXCEPT('Table','Table'[Date],'Table'[Location]))/SELECTEDVALUE('Table'[Max occupancy])

Result would be shown as below.

3.PNG 

 

Best Regards,

jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

5 REPLIES 5
petermb72
Helper III
Helper III

The desired output would be showing what my occupancy based on rooms, not ID numbers is each day.  So for instance:

 

Occupancy Percentages:

9/29 Fargo 1 divided by 140
9/30 Fargo 1 divided by 140
10/1 Fargo 1 divided by 140
9/29 Mooread 3 divided by 115 (ID 500 has two on the same date, that is a shared room I want to only count as 1)
10/1 Moorhead 3 divided by 108 (ID 500 is doubled up on same date so they are in one unit and the max occupancy has shrunk to 108 vs 115)

 

So what I want to get for the max occupancy is

9/29 Fargo Occ = .7%
9/29 Moorhead Occ = 2.6%

10/1 Fargo Occ = .7%

10/1 Moorhead = 2.78%

 

Does this help?

Hi @petermb72 ,

 

Please check the following measure.

Measure = CALCULATE(DISTINCTCOUNT('Table'[ID]),ALLEXCEPT('Table','Table'[Date],'Table'[Location]))/SELECTEDVALUE('Table'[Max occupancy])

Result would be shown as below.

3.PNG 

 

Best Regards,

jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Thanks for that.

A measure like this

Measure = DIVIDE(DISTINCTCOUNT(TableGH[ID]) ,MIN(TableGH[Max occupancy]))

Alter the Measure Format for decimal places/percentage 

petermb72
Helper III
Helper III

I know the subject description stinks, but comeone one of you people with huge brains surely know the answer to this.........

I think the huge brains will know the answer but they might not know the question!

 

Perhaps you could show the desired output from the data sample (with an explanation of anything not obvious).  I'm sure someone can help you.

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors