Count number of orders for each restaurant and multiply by min and max product price then sum

[twixy]

Hi Community,

I would like to generate a plot of the minimum and maximum revenue for each restaurant that can be plotted on a line chart in powerbi and then is filterable by restaurant etc. I have looked into using measures, calculate, filterby, sumx and related etc. but am not quite sure how to put it together. Please help!

I have tables A and B where they are linked by the column Order in Table A to column Product in Table B. I would like to generate the following:

Restaurant	Min. Revenue	Max. Revenue
Restaurant A	1x$10 + 1x$0.40 = $10.40	1x$15 + 1x$0.60 = $15.60
Restaurant B	2 x $10 = $20	$2 x $15 = $30

Table A

Restaurant	Order
Restaurant A	Hamburger
Restaurant A	Apple
Restaurant B	Hamburger
Restaurant B	Hamburger

Table B

Product	Min. Price	Max Price
Apple	$0.40	$0.60
Hamburger	$10	$15
Water	$1	$2

Thanks for any help you can provide!

[FarhanJeelani]

Dear @twixy ,

It looks like the original DAX formula you received isn’t returning values due to issues with the RELATEDTABLE() function. Please try using SUMX and LOOKUPVALUE to achieve the desired output.

Corrected DAX Measures for Min and Max Revenue
Step 1: Create a Measure for Minimum Revenue

MinRevenue =
SUMX(
'Table A',
LOOKUPVALUE('Table B'[Min. Price], 'Table B'[Product], 'Table A'[Order])
)

Step 2: Create a Measure for Maximum Revenue

MaxRevenue =
SUMX(
'Table A',
LOOKUPVALUE('Table B'[Max Price], 'Table B'[Product], 'Table A'[Order])
)

Please mark this post as solution if it helps you. Appreciate Kudos.

[twixy]

Thanks @FarhanJeelani ! I realized that I had a data input error that messed up the relatedtable function. Now that I have fixed it, I'm tryin @bhanu_gautam's suggested DAX code and it is showing numbers but not the right one.

What I am getting back for Restaurant B is:

Restaurant	Min. Revenue	Max. Revenue
Restaurant B (What I see now)	$10	$15
Restaurant B (What I want to get)	$10 x 2 = $20	$15 x 2 = $30

[FarhanJeelani]

Hi @twixy,

try 
Minimum Revenue:

MinRevenue =
SUMX (
VALUES ( 'Table A'[Order] ), -- Get unique orders
COUNTROWS ( FILTER ( 'Table A', 'Table A'[Order] = SELECTEDVALUE ( 'Table A'[Order] ) ) )
* LOOKUPVALUE ( 'Table B'[Min. Price], 'Table B'[Product], 'Table A'[Order] )
)

Maximum Revenue:

MaxRevenue =
SUMX (
VALUES ( 'Table A'[Order] ), -- Get unique orders
COUNTROWS ( FILTER ( 'Table A', 'Table A'[Order] = SELECTEDVALUE ( 'Table A'[Order] ) ) )
* LOOKUPVALUE ( 'Table B'[Max Price], 'Table B'[Product], 'Table A'[Order] )
)

Please mark this post as solution if it helps you. Appreciate Kudos.

[twixy]

Hi @FarhanJeelani, I tried that but it does not return any values. Is there any way to edit the below code to get it to return the correct min revenue?

MinRevenue =
SUMX(
SUMMARIZE(
'Table A',
'Table A'[Restaurant],
'Table A'[Order],
"MinPrice", MINX(RELATEDTABLE('Table B'), 'Table B'[Min. Price])
),
[MinPrice]

 Create a measure for the maximum revenue

DAX
MaxRevenue =
SUMX(
SUMMARIZE(
'Table A',
'Table A'[Restaurant],
'Table A'[Order],
"MaxPrice", MAXX(RELATEDTABLE('Table B'), 'Table B'[Max Price])
),
[MaxPrice]

[FarhanJeelani · Accepted solution]

Hi @twixy ,The issue seems to be that the formulas do not correctly account for multiple orders per restaurant. You need to count the number of times each product appears in Table A, then multiply it by the Min Price and Max Price from Table B.

Minimum Revenue:

MinRevenue =
SUMX(
SUMMARIZE(
'Table A',
'Table A'[Restaurant],
'Table A'[Order],
"MinPrice", MINX(RELATEDTABLE('Table B'), 'Table B'[Min. Price]),
"OrderCount", COUNTROWS(FILTER('Table A', 'Table A'[Order] = EARLIER('Table A'[Order])))
),
[MinPrice] * [OrderCount]
)

Maximum Revenue:

MaxRevenue =
SUMX(
SUMMARIZE(
'Table A',
'Table A'[Restaurant],
'Table A'[Order],
"MaxPrice", MAXX(RELATEDTABLE('Table B'), 'Table B'[Max Price]),
"OrderCount", COUNTROWS(FILTER('Table A', 'Table A'[Order] = EARLIER('Table A'[Order])))
),
[MaxPrice] * [OrderCount]
)

[twixy]

That worked great @FarhanJeelani! Thank you for your patience with this and sharing your expertise!!

[twixy]

@FarhanJeelani, what would be your suggestion if I also wanted to then sum the min revenue across all restaurants? (i.e. get total min revenue = $10.40+$20 = $30.40 and total max revenue = $15.60+$30) Thanks!

[FarhanJeelani]

@twixy , To sum the minimum and maximum revenue across all restaurants, you can modify the measures by removing the grouping by the restaurant and simply summing up the results.

Let me know if you need dax for this.

[FarhanJeelani]

Dear @twixy , Please mark my post as solution if it helps you.

[twixy]

@FarhanJeelani , got it thanks! Also marked post as solution!

[twixy]

If I were in excel and trying to solve this in an inelegant manner, I would create a new column, for each row, use vlookup to find the min.Price or max.Price and return that into the column. Then I would sum if with the condition of the restaurant name. I haven't quite figured out if I should do something similar in powerbi.

[bhanu_gautam]

@twixy 

Create a measure for the minimum revenue
MinRevenue =
SUMX(
SUMMARIZE(
'Table A',
'Table A'[Restaurant],
'Table A'[Order],
"MinPrice", MINX(RELATEDTABLE('Table B'), 'Table B'[Min. Price])
),
[MinPrice]

 Create a measure for the maximum revenue

DAX
MaxRevenue =
SUMX(
SUMMARIZE(
'Table A',
'Table A'[Restaurant],
'Table A'[Order],
"MaxPrice", MAXX(RELATEDTABLE('Table B'), 'Table B'[Max Price])
),
[MaxPrice]

 Create a table visual in Power BI
Add a table visual to your report.
Add the Restaurant column from Table A to the table visual.
Add the MinRevenue and MaxRevenue measures to the table visual

[twixy]

Hi @bhanu_gautam , thanks for the quick response! For some reason the measure does not output a number and when I put it into a table it shows up a blank value. Not sure if something else I am doing is wrong - I checked the data type for the min and max price and they are all in decimal places. For the relationship, I've tried both one way and both ways but still can't get it to work. Any ideas what I might be missing? Thanks for your help!

[bhanu_gautam]

@twixy Please find the attached PBIX