topic Grouping to get by date for getting percentages in Desktop
https://community.powerbi.com/t5/Desktop/Grouping-to-get-by-date-for-getting-percentages/m-p/1413921#M597917
<P>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:</P><P> </P><TABLE border="1"><TBODY><TR><TD>Date</TD><TD>Location</TD><TD>ID</TD><TD>Max occupancy</TD><TD> </TD><TD>Date</TD><TD>Location</TD><TD>ID</TD><TD>Max Occupancy</TD><TD> </TD></TR><TR><TD>9/29/2020</TD><TD>Fargo</TD><TD>101</TD><TD>140</TD><TD> </TD><TD>10/1/2020</TD><TD>Moorhead</TD><TD>502</TD><TD>108</TD><TD> </TD></TR><TR><TD>9/30/2020</TD><TD>Fargo</TD><TD>102</TD><TD><P>140</P></TD><TD> </TD><TD>10/1/2020</TD><TD>Moorhead</TD><TD>503</TD><TD>108</TD><TD> </TD></TR><TR><TD>10/1/2020</TD><TD>Fargo</TD><TD>300</TD><TD>140</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>9/29/2020</TD><TD>Moorhead</TD><TD>500</TD><TD>115</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>9/29/2020</TD><TD>Moorhead</TD><TD>500</TD><TD>115</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>9/29/2020</TD><TD>Moorhead</TD><TD>502</TD><TD><P>115</P></TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>9/29/2020</TD><TD>Moorhead</TD><TD>503</TD><TD>115</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>10/1/2020</TD><TD>Moorhead</TD><TD>500</TD><TD>108</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>10/1/2020</TD><TD>Moorhead</TD><TD>500</TD><TD>108</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE><P> </P><P>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. </P><P>Issue number 1. Numerator needs to be 1 count per unit per day. </P><P>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. </P><P> </P><P>Thanks for any help.</P><P>Peter</P><P> </P>Mon, 05 Oct 2020 21:32:21 GMTpetermb722020-10-05T21:32:21ZGrouping to get by date for getting percentages
https://community.powerbi.com/t5/Desktop/Grouping-to-get-by-date-for-getting-percentages/m-p/1413921#M597917
<P>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:</P><P> </P><TABLE border="1"><TBODY><TR><TD>Date</TD><TD>Location</TD><TD>ID</TD><TD>Max occupancy</TD><TD> </TD><TD>Date</TD><TD>Location</TD><TD>ID</TD><TD>Max Occupancy</TD><TD> </TD></TR><TR><TD>9/29/2020</TD><TD>Fargo</TD><TD>101</TD><TD>140</TD><TD> </TD><TD>10/1/2020</TD><TD>Moorhead</TD><TD>502</TD><TD>108</TD><TD> </TD></TR><TR><TD>9/30/2020</TD><TD>Fargo</TD><TD>102</TD><TD><P>140</P></TD><TD> </TD><TD>10/1/2020</TD><TD>Moorhead</TD><TD>503</TD><TD>108</TD><TD> </TD></TR><TR><TD>10/1/2020</TD><TD>Fargo</TD><TD>300</TD><TD>140</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>9/29/2020</TD><TD>Moorhead</TD><TD>500</TD><TD>115</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>9/29/2020</TD><TD>Moorhead</TD><TD>500</TD><TD>115</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>9/29/2020</TD><TD>Moorhead</TD><TD>502</TD><TD><P>115</P></TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>9/29/2020</TD><TD>Moorhead</TD><TD>503</TD><TD>115</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>10/1/2020</TD><TD>Moorhead</TD><TD>500</TD><TD>108</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>10/1/2020</TD><TD>Moorhead</TD><TD>500</TD><TD>108</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE><P> </P><P>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. </P><P>Issue number 1. Numerator needs to be 1 count per unit per day. </P><P>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. </P><P> </P><P>Thanks for any help.</P><P>Peter</P><P> </P>Mon, 05 Oct 2020 21:32:21 GMThttps://community.powerbi.com/t5/Desktop/Grouping-to-get-by-date-for-getting-percentages/m-p/1413921#M597917petermb722020-10-05T21:32:21ZRe: Grouping to get by date for getting percentages
https://community.powerbi.com/t5/Desktop/Grouping-to-get-by-date-for-getting-percentages/m-p/1415667#M598396
<P>I know the subject description stinks, but comeone one of you people with huge brains surely know the answer to this.........</P>Tue, 06 Oct 2020 14:17:47 GMThttps://community.powerbi.com/t5/Desktop/Grouping-to-get-by-date-for-getting-percentages/m-p/1415667#M598396petermb722020-10-06T14:17:47ZRe: Grouping to get by date for getting percentages
https://community.powerbi.com/t5/Desktop/Grouping-to-get-by-date-for-getting-percentages/m-p/1415906#M598477
<P>I think the huge brains will know the answer but they might not know the question!</P>
<P> </P>
<P>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.</P>Tue, 06 Oct 2020 15:53:25 GMThttps://community.powerbi.com/t5/Desktop/Grouping-to-get-by-date-for-getting-percentages/m-p/1415906#M598477HotChilli2020-10-06T15:53:25ZRe: Grouping to get by date for getting percentages
https://community.powerbi.com/t5/Desktop/Grouping-to-get-by-date-for-getting-percentages/m-p/1415981#M598500
<P>The desired output would be showing what my occupancy based on rooms, not ID numbers is each day. So for instance:</P><P> </P><P>Occupancy Percentages:</P><P>9/29 Fargo 1 divided by 140<BR />9/30 Fargo 1 divided by 140<BR />10/1 Fargo 1 divided by 140<BR />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)<BR />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)</P><P> </P><P>So what I want to get for the max occupancy is</P><P>9/29 Fargo Occ = .7%<BR />9/29 Moorhead Occ = 2.6%</P><P>10/1 Fargo Occ = .7%</P><P>10/1 Moorhead = 2.78%</P><P> </P><P>Does this help?</P>Tue, 06 Oct 2020 16:47:01 GMThttps://community.powerbi.com/t5/Desktop/Grouping-to-get-by-date-for-getting-percentages/m-p/1415981#M598500petermb722020-10-06T16:47:01ZRe: Grouping to get by date for getting percentages
https://community.powerbi.com/t5/Desktop/Grouping-to-get-by-date-for-getting-percentages/m-p/1416599#M598675
<P>Thanks for that.</P>
<P>A measure like this</P>
<LI-CODE lang="markup">Measure = DIVIDE(DISTINCTCOUNT(TableGH[ID]) ,MIN(TableGH[Max occupancy]))</LI-CODE>
<P>Alter the Measure Format for decimal places/percentage </P>Tue, 06 Oct 2020 23:48:09 GMThttps://community.powerbi.com/t5/Desktop/Grouping-to-get-by-date-for-getting-percentages/m-p/1416599#M598675HotChilli2020-10-06T23:48:09ZRe: Grouping to get by date for getting percentages
https://community.powerbi.com/t5/Desktop/Grouping-to-get-by-date-for-getting-percentages/m-p/1427557#M601668
<P>Hi <LI-USER uid="165812"></LI-USER> ,</P>
<P> </P>
<P>Please check the following measure.</P>
<LI-CODE lang="markup">Measure = CALCULATE(DISTINCTCOUNT('Table'[ID]),ALLEXCEPT('Table','Table'[Date],'Table'[Location]))/SELECTEDVALUE('Table'[Max occupancy])</LI-CODE>
<P>Result would be shown as below.</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="3.PNG" style="width: 242px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/374355i7760D46D8C838305/image-size/medium?v=1.0&px=400" role="button" title="3.PNG" alt="3.PNG" /></span> </P>
<P> </P>
<P>Best Regards,</P>
<P>jay</P>Mon, 12 Oct 2020 08:52:07 GMThttps://community.powerbi.com/t5/Desktop/Grouping-to-get-by-date-for-getting-percentages/m-p/1427557#M601668v-jayw-msft2020-10-12T08:52:07Z