cancel
Showing results for
Did you mean:
Highlighted
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:

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

And got:

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

And got:

What am I doing wrong here?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
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]))```

Highlighted
Solution Specialist

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

@Christann Instead of distinctcount just use count.

11 REPLIES 11
Highlighted
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

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

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

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

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:

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

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

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

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

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]))```

Highlighted
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?

Announcements

Power Platform Community Conference

Check out the on demand sessions that are available now!

Experience what’s next for Power BI

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

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021