Forum Discussion
Multiply 2 columns from 2 different tables
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
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_
13 Replies
Hey,
I'm wondering if both tables are related?
Regards,
Tom
yes, they are related. Thank you
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
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.
