Using a constant in a measure

[petermb72]

Here is some of the data that I am working with.  It is a very simple question but I am just not able to wrap my head around how to fix it.

Location	Max OCC
Fargo	8
Moorhed	4
West Fargo	12

Location	Max Occ	Room #	Date
Fargo	8	1	12/1/2019
Fargo	8	3	12/1/2019
Fargo	8	4	12/1/2019
Fargo	8	7	12/1/2019
Fargo	8	8	12/1/2019
Fargo	8	1	12/2/2019
Fargo	8	3	12/2/2019
Fargo	8	2	12/2/2019
Fargo	8	4	12/2/2019

The tables are lined by location.  I have two additional measures that tells me how many days in the month, as well as a measure that will sum up the total number of rooms rented in the month.  The issue comes in with the Max Occupancy.  I want my formula to work like this:

Average Monthly OCC = divide([sumofroomsrented],([#daysinaMonth]*Max Occupancy

Its the Max Occupancy that I have an issue with.  How do I get the Measure just to use the one Max Occupancy of 8?  I have other locations with other max occupancies so as I use my slicer it needs to pick the correct max occupancy.  Should be easy but the answer is not coming to me.

[v-alq-msft]

Hi, @petermb72 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. Thanks.

Best Regards,

Allan

[Ashish_Mathur]

Hi,

In a Table format, show thr exact result that you are expecting.

[petermb72]

Location	Max OCC
Fargo	8
Moorhead	14
West Fargo	21

location	Room #	Date
Fargo	1	12/1/2019
Fargo	3	12/1/2019
Fargo	4	12/1/2019
Fargo	5	12/1/2019
Fargo	6	12/1/2019
Fargo	7	12/1/2019
Fargo	8	12/1/2019
West Fargo	2	12/1/2019
West Fargo	3	12/1/2019
West Fargo	4	12/1/2019
Moorhead	2	12/1/2019
Moorhead	3	12/1/2019
Moorhead	4	12/1/2019
Moorhead	5	12/1/2019
Moorhead	6	12/1/2019
Moorhead	7	12/1/2019
Fargo	1	12/2/2019
Fargo	2	12/2/2019
Fargo	3	12/2/2019
Fargo	4	12/2/2019
West Fargo	1	12/2/2019
West Fargo	2	12/2/2019
West Fargo	3	12/2/2019
West Fargo	4	12/2/2019
Moorhead	1	12/2/2019
Moorhead	2	12/2/2019
Moorhead	3	12/2/2019
Moorhead	4	12/2/2019
Moorhead	5	12/2/2019

I want to show Total number of rented rooms/max number of rooms per location.  I want to be able to use a slicer to choose what location.  The issue I have is that when I say I want Max Occ.  I get the max occupancy of the location table for all locations.  Not just the selected location in the slicer.  So for example if I wanted to get the percentage occupancy for the 12/1/2019 for Moorhead,  I would set slicer to the correct date, set slicer to Moorhead(Location Table),  I would then have a measure that would be Sum of Room #/Max OCC.  So I want 6/14 to give me a pertencage of 42.8%.  What I am getting is 6/21.  The 21 being the max occupancy of the location table. 

I hope this brings a little more clarity as to what I am looking for.

Thanks,

Peter

[petermb72]

I also have a date table that says Dec has 31 Days, January has 31 days ect.....

So the calulation would be:  Count Rooms rented/ (number of days in month*maxocc)

or for the YTD  Count of rooms rented for year/(number of days in ytd*maxocc)

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[TomMartens]

Hey, 

from my understanding you are pulling the column MAX OCC from the table of the one side of the relationship to the manyside.

If this assumption is correct give this a try:

Average Monthly OCC = 
	AVERAGEX(
		'<table on the many side>'
		, divide([sumofroomsrented],([#daysinaMonth]*RELATED('tableontheoneside>'[Max Occupancy])))
	)

Hopefully this provides what you are looking for.

Regards,
Tom

[v-alq-msft]

Hi, @petermb72 

For the example of two tables you have created, you can create the measures as follows.

daysinaMonths = Day(Date(Year(max('Table B'[Date])),month(max('Table B'[Date]))+1,1)-1)

sumofroomsrented = CALCULATE(SUM('Table B'[Room]),FILTER(ALLSELECTED('Table B'),'Table B'[Location] in FILTERS('Table A'[Location])))

Max Occupancy = MAX('Table B'[Max Occ])

Average Monthly OCC = DIVIDE([sumofroomsrented],[daysinaMonths]*[Max Occupancy])

Result:

If I misunderstand the your thoughts, please inform me of your expected output. I am glad to solve the problem for you.

Best Regards,

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[petermb72]

Allen, 

Thanks for responding.  THe issue I am having is sitll with the max occupancy.  I have several locations all with different max occupancies.  if I use MAX (MaxOCC), I am not going to get the max for the right property chosen when I use a slicer to choose the single property.  I will always get the max.  Does that make sence?

[petermb72]

Allen I think we are just about there.  Do I need to try to explain the issue any more?  I think I just need a last little step to figure this out.