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

[Christann]

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?

Thanks in advance for your help!

[Anonymous · Accepted solution]

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

[Anonymous]

@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))

[Christann]

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!

[Anonymous]

@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. 

[Christann]

Here is a link to a file containing the data.

[Anonymous · Accepted solution]

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

[Christann]

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?

[Anonymous · Accepted solution]

@Christann Instead of distinctcount just use count. 

[Anonymous]

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

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

[v-danhe-msft]

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

[Christann]

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

[v-danhe-msft]

Hi @Christann,

You could try this measure:

Measure = CALCULATE(COUNT(RoomAssignment[RateCode]),FILTER('RoomAssignment','RoomAssignment'[RateCode]=MAX('RoomRates'[RateCode])))*CALCULATE(SUM(RoomRates[Rate]))

Result:

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/u8e34tqk4zei965/How%20can%20I%20get%20a%20measure%20which%20multiplies%20c...

Regards,

Daniel He