Multiply 2 columns from 2 different tables

[electrobrit]

I have Hotel Table1: "# of Rooms" in a table and in another table (dimDate table) "DaysinMonth"
I want to get # of rooms available in a month so if one table has a hotel with 100 rooms and there are 31 days in the selected month, it's 10*31 = 310 avail rooms.
I've tried measures, calc columns. I can't just simply multiply these 2 columns. What am I doing wrong? Please help

[Cmcmahan]

In PowerBI, you (usually) can't multiply two columns by each other.  It doesn't make sense to be able to have [ColA] * [ColB] return a value.  Would it multiply the first row in A, times the first row in B?  What happens when they don't have the same amount of rows? Would it multiply every value in A by every value in B? How would you navigate the results?   What you can do is multiply single values.  You can easily multiply the SUM([ColA]) * AVERAGE([ColB]) because PBI knows how to get a single value for each of those.  

A quick and easy way to set this up where the answer changes based on the current selection of values is to use a measure. Measures are great when you use them in the proper context.  It's like quickly and easily asking PowerBI "What is the current sum of A times the current average of B?"  The word current here is key.  

However, measures only work when you're in a context that makes sense.  Measures aren't good at answering the question "What is the current sum of A times each value of B?" since that isn't a single value. In your case, if you're trying to get a count of rooms multiplied by the number of days in a month, you can do something simple like this:

RoomsAvail = COUNTROWS('dimDate') * SELECTEDVALUE('HotelTable1'[# of rooms])

Then if you put this into a visual (likely a table or Matrix) and group it by month and HotelID, it will display the proper value. PowerBI sets up the context each time you want the result of the measure for you! Keep in mind, that this will only return correct values when A) your context is already filtered down to the individual month and B) your context is already filtered down to one Hotel. 

If you're trying to get a count of all rooms availble for multiple hotels at a time, you can use another aggregation (like SUM) in place of SELECTEDVALUE. This has the benefit of not breaking even at a top level context, where you haven't selected a hotel/month, but will give you a very large (and likely useless) result, since it would be multiplying the total number of days in your date dimension times the sum total of rooms available in every hotel.

How/when are you trying to display/use this value? This changes how you have to set up the measure immensely, and lets us give you a more personalized answer.  That's why users keep asking for information about how [# of Rooms Avail] is related to your date dimension.  This is a useful guide on what information can help us answer you quickly and succinctly. 

[TomMartens]

Hey, 

I'm wondering if both tables are related?

Regards,

Tom

[electrobrit]

yes, they are related. Thank you

[TomMartens]

Hey,

please excuse for being not precise:

• create a screenshot of the relationship
• describe the relationship
• name the columns that are forming the relationship
• consider to create a pbix with sample data, upload the file to onedrive or dropbox and share the link

Regards,

Tom

[electrobrit]

@TomMartens thank you for your help, I knew I was missing something. I have created a test pbix 
Ultimately, it would allow me to do available rooms in selected month as below. 
https://www.dropbox.com/sh/10f3lpyzcmw6qp0/AAD2frS1O9wyRBhKAoqHItK0a?dl=0

HotelCode	# of GuestRooms	date	#of days in month (selected)	avail rooms in selected month
bbb	10	4/1/2000	30	300
ccc	30	4/1/2000	30	900
ddd	20	4/1/2000	30	600

@Cmcmahan thank you for your feedback too

[Cmcmahan]

No problem. I hope my explanation of context was helpful!

If this problem has been solved, please close the topic by selecting a Solution.  If there are futher questions, let us know!

[electrobrit]

no it is not solved.

[Cmcmahan · Accepted solution]

My apologies, I thought you had created that table in your .pbix file. I wasn't aware that was still a desired outcome.

Opening your .pbix file, I just created a measure exactly like my previous post, using values you had in your Date Dimension instead of calculating the days in the month by counting rows, and added that to the table:

Avail Rooms = SELECTEDVALUE(DimDate[DaysinMonth]) * SELECTEDVALUE('HotelTable'[#Guestrooms])

Here's an updated .pbix file: https://drive.google.com/open?id=1oavdU__k8V2gzCLv2Jl1qyVvbH9MWqt_

[electrobrit]

@Cmcmahan thank you for the formula. Your previous post had added countrows for dimdate and didn't make sense to me. This worked as (almost) desired outcome. It still doesn't change if people select multiple months, like I was mentioning to @TomMartens .  That seems like it should be so easy, but I guess it's not. Thanks again.

[Cmcmahan]

Yeah, I was trying to be cheeky with the whole counting of rows thing.  I had assumed your dimDate didn't have a DaysInMonth field.  I also figured you would slice by the month value instead of by specific dates, so the count would work.  

Too many assumptions on my end.

[TomMartens]

Hey @electrobrit 

I'm wondering what should happen if there is more than one month covered by the slicer selection (either full or partial)?

Nevertheless, this measure considers the average of DaysInMonth across all selected days from the slicer:

Measure = 
var theNoOfDays = CALCULATE(AVERAGEX('DimDate' , [DaysinMonth]))
return
SUMX(
    VALUES('RateData'[HotelCode])
    , var _HotelCode = [HotelCode]
    var _NoOfRooms = LOOKUPVALUE('HotelTable'[#Guestrooms] , HotelTable[HotelCode] , _HotelCode)
    return
    theNoOfDays * _NoOfRooms
)

Regards,
Tom

[electrobrit]

@TomMartens In theory for the report needed, people are only looking at a month at a time so never would be actually looking at that. However, is there not a way to multipy # of guestrooms * days in month (or from what is selected in the date slicer) that determines that days in month field (this field could theoretically be called "days in date selection" since the user could select more than one month)?

[Cmcmahan]

Also, it's worth noting that your Days in month in that card is doing a count of your Date Dimension where there is a value for [DaysInMonth], so it's literally counting the number of days between your slicer selection dates. 

I would change your slicer from a date range into a hierarchy slicer. In the dropdown menu on the field, set the date hierarchy and select the month option to slice by month.  You may need a separate year slicer to slice years once you start having multiple years worth of data.