cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

How can I get a measure which multiplies columns from several related tables?

How can I create a measure which calculates my total Revenue? I have a table "RoomAssignments" which is related to the table "RoomRates" by a column "Rate Code". I am trying to create a measure which will multiply a count of the rows in RoomAssignments with the corresponding rate in RoomRates. Here is a screenshot of the relationship:

 

related.JPG

 

 

 

I tried: Total Revenue = COUNT(RoomAssignments[RateCode])*RELATED(Rate)

And got:

 

related f 1.JPG

 

 

Then I tried: Total Revenue = COUNT(RoomAssignments[RateCode])*RELATED(RoomRates[Rate])

And got:

 

related f 2.JPG

 

 

What am I doing wrong here?

Thanks in advance for your help!

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Solution Specialist
Solution Specialist

Re: How can I get a measure which multiplies columns from several related tables?

@Christann I created the first 3 columns in RoomAssignment Table & Data is the calculated table. 

 

Rooms Sold Per RateCode = CALCULATE(DISTINCTCOUNT(RoomAssignment[RoomID]), ALLEXCEPT(RoomAssignment,RoomAssignment[RateCode]))
Rate = RELATED(RoomRates[Rate])
Total Sales Per RateCode = RoomAssignment[Rate]*RoomAssignment[Rooms Sold Per RateCode]

 

 

Data = DISTINCT(SELECTCOLUMNS(RoomAssignment,"Rate Code",RoomAssignment[RateCode],"Rate",RoomAssignment[Rate],"Rooms Sold",RoomAssignment[Rooms Sold Per RateCode],"Total Sales",RoomAssignment[Total Sales Per RateCode]))

 

View solution in original post

Highlighted
Solution Specialist
Solution Specialist

Re: How can I get a measure which multiplies columns from several related tables?

@Christann Instead of distinctcount just use count. 

View solution in original post

11 REPLIES 11
Highlighted
Microsoft
Microsoft

Re: How can I get a measure which multiplies columns from several related tables?

Hi @Christann,

I could not see the picture you have posted in your probem, could you mind uploading your pictures again or share the pbix file if possible?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper IV
Helper IV

Re: How can I get a measure which multiplies columns from several related tables?

I have Edited the post and re-entered my screenshots. Tell me if they don't show up for you.

Highlighted
Solution Specialist
Solution Specialist

Re: How can I get a measure which multiplies columns from several related tables?

@Christann 

Create the following columns

1) Rate = Related(Rate)

2) CountofRooms = CALCULATE(DistinctCount(RoomId), ALLEXCEPT(RateCode))

3) Create a calculatetable = DISTINCT(RateCode, Rate, CountofRooms)

4) Final Calculation CALCULATE(SUMX(Rate*CountofRooms))

Highlighted
Helper IV
Helper IV

Re: How can I get a measure which multiplies columns from several related tables?

Is there a way to do this with only a measure?

 

I tried your solution, and got this error:

 

Count of Rooms.JPG

 

Also, would it work better to insert a calculated column into the RoomRates table instead of creating a calculated table?

Thanks so much for your help!

Highlighted
Solution Specialist
Solution Specialist

Re: How can I get a measure which multiplies columns from several related tables?

Your all except syntax is wrong. Check the below post for syntax.

 

https://msdn.microsoft.com/en-us/query-bi/dax/allexcept-function-dax

Highlighted
Solution Specialist
Solution Specialist

Re: How can I get a measure which multiplies columns from several related tables?

@Christann yeah I think you can do the last step without a table, you would just need to create a distinct filter. 

 

If you post in the power bi file its easier to provide a solution. 

Highlighted
Helper IV
Helper IV

Re: How can I get a measure which multiplies columns from several related tables?

Here is a link to a file containing the data.

Highlighted
Solution Specialist
Solution Specialist

Re: How can I get a measure which multiplies columns from several related tables?

@Christann I created the first 3 columns in RoomAssignment Table & Data is the calculated table. 

 

Rooms Sold Per RateCode = CALCULATE(DISTINCTCOUNT(RoomAssignment[RoomID]), ALLEXCEPT(RoomAssignment,RoomAssignment[RateCode]))
Rate = RELATED(RoomRates[Rate])
Total Sales Per RateCode = RoomAssignment[Rate]*RoomAssignment[Rooms Sold Per RateCode]

 

 

Data = DISTINCT(SELECTCOLUMNS(RoomAssignment,"Rate Code",RoomAssignment[RateCode],"Rate",RoomAssignment[Rate],"Rooms Sold",RoomAssignment[Rooms Sold Per RateCode],"Total Sales",RoomAssignment[Total Sales Per RateCode]))

 

View solution in original post

Highlighted
Helper IV
Helper IV

Re: How can I get a measure which multiplies columns from several related tables?

It looks to me as if this solution does not take into account the fact that each room is let out several times. For instance, There are 7 "W" rooms and the Rooms Sold per RateCode only takes into account each room once, thus giving the result 7 when the actual answer is 561. How would I fix this?

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors