topic Re: Count number of orders for each restaurant and multiply by min and max product price then sum in DAX Commands and Tips

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

twixy — Thu, 20 Feb 2025 07:25:16 GMT

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!

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

bhanu_gautam — Thu, 20 Feb 2025 07:36:11 GMT

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

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

twixy — Thu, 20 Feb 2025 10:29:39 GMT

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!

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

twixy — Thu, 20 Feb 2025 10:37:10 GMT

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.

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

FarhanJeelani — Thu, 20 Feb 2025 11:04:20 GMT

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.

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

twixy — Thu, 20 Feb 2025 11:54:35 GMT

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

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

FarhanJeelani — Thu, 20 Feb 2025 12:07:34 GMT

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.

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

twixy — Thu, 20 Feb 2025 12:44:51 GMT

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]

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

FarhanJeelani — Thu, 20 Feb 2025 12:53:15 GMT

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

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

twixy — Thu, 20 Feb 2025 13:06:05 GMT

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

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

FarhanJeelani — Thu, 20 Feb 2025 13:09:10 GMT

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

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

twixy — Thu, 20 Feb 2025 13:09:45 GMT

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!

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

FarhanJeelani — Thu, 20 Feb 2025 13:12:18 GMT

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.

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

bhanu_gautam — Thu, 20 Feb 2025 13:28:49 GMT

twixy Please find the attached PBIX

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

twixy — Thu, 20 Feb 2025 13:38:51 GMT

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