Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

A measure (NR impact) based on measures from two different tables

Hello everyone,

 

I need to calculate a Net Revenue impact, based on measures calculated in two different tables.

 

1. I have a calculated table, that consists of Account (Customer), Product, and Average Price:

 

LStoychev_2-1695457617012.png

 

Based on this table I calculated:

1. the average of average price (like in Pivot table) for all clients, except for Client D

= CALCULATE(AVERAGE('Average of Contract price'[Average Price]),'Average of Contract price'[Account]<>"Client D")

2. the average of average price for Client D

= CALCULATE(AVERAGE('Average of Contract price'[Average Price]),'Average of Contract price'[Account]="Client D")

3. Price Delta (Avg. price for Client D - Avg. price for the rest of the clients)

= 'Average of Contract price'[AveragePrice2]-'Average of Contract price'[AveragePrice1]

 

The other table contains quantities for Client D (column Volume):

LStoychev_4-1695458132998.png

 
I created a simple measure that sums the quantities for Client D.
 
What I need is to multiply the Price Delta (the third masure) with the SUM of the quantities for client D in order to get the Net Revenue impact.
 
Thanks in advance.
 

 

 

 

1 ACCEPTED SOLUTION
123abc
Community Champion
Community Champion

I see that you want to calculate the Price Delta and the Net Revenue impact per product and not aggregate it across all customers/dates. To achieve this, you need to use context transition functions in DAX to perform the calculations at the product level. You can modify your measures as follows:

Assuming you have a product identifier (let's call it "ProductID") in your tables, you can create the following measures:

  1. Price Delta per Product:

Price Delta per Product =
SUMX(
FILTER('Average of Contract price', 'Average of Contract price'[Account] = "Client D"),
'Average of Contract price'[AveragePrice2] - 'Average of Contract price'[AveragePrice1]
)

 

This measure calculates the Price Delta for each product separately by iterating over the 'Average of Contract price' table and applying a filter for "Client D."

  1. Total Quantity per Product for Client D (assuming you have a 'Volume' table with a relationship to your product table):

Total Quantity per Product Client D =
SUMX(
FILTER('Volume', 'Volume'[Customer] = "Client D"),
'Volume'[Volume]
)

 

This measure calculates the total quantity for Client D per product.

  1. Net Revenue Impact per Product:

Net Revenue Impact per Product = [Price Delta per Product] * [Total Quantity per Product Client D]

 

Now, when you use the "Net Revenue Impact per Product" measure in your visuals, it should correctly calculate the impact at the product level, as shown in your Excel example.

View solution in original post

8 REPLIES 8
parry2k
Super User
Super User

@Anonymous Great you have the solution, the only thing I will add is that you don't need to create a calculated table for this calculation. If the purpose of that table is just to go net impact then it can be done using a measure without a calculated table—just my 2 cents.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@Anonymous I don't think you need to create a calculated table to achieve the goal. It will be easier if you can share pbix file with sample data with the expected output and I will get you the solution. You can share using one drive/google drive.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Thanks Parry2k,

 

You can find below 2 links. The first one is for the PBI dashboard, the second one contains a sample data.

I wanted to calculate the avg. of the avg. product prices for all customers, except for Client D. - I have already done this (see the measure AveragePrice2, used in the first visual).

Then I subtracted from it the average prices of Client D (and I got the Price Delta for all products).

 

What I want to do now, is to multiply the Client D's volume by the Price Delta (in order to get the NR impact). 

The NR impact that you see in the second visual is calculated in Excel, not in PBI. I want to calculate it, using PBI.

 

Thanks again.

 

https://drive.google.com/file/d/1xlWWVhkn9aSuE1SAYcrWwIvJYvVFZ2QS/view?usp=drive_link

 

https://docs.google.com/spreadsheets/d/1tfqQKQBf7AwtSUtSOylo_r9Sij0mxqWk/edit?usp=drive_link&ouid=11...

123abc
Community Champion
Community Champion

To calculate the Net Revenue impact by multiplying the Price Delta with the SUM of quantities for Client D, you can create a new measure in your Power BI report. Based on the measures and tables you provided, here's how you can create this new measure:

Assuming your calculated table with the Price Delta is called "Average of Contract price," and the table with quantities for Client D is called something like "Client D Quantities," you can create a measure as follows: 

 

Net Revenue Impact =
VAR PriceDelta = 'Average of Contract price'[AveragePrice2] - 'Average of Contract price'[AveragePrice1]
VAR ClientDQuantity = SUM('Client D Quantities'[Volume])
RETURN
PriceDelta * ClientDQuantity

 

This DAX measure first calculates the Price Delta using the Price Delta formula you provided. Then, it calculates the SUM of quantities for Client D from the "Client D Quantities" table. Finally, it returns the product of PriceDelta and ClientDQuantity, which gives you the Net Revenue impact you're looking for.

You can add this measure to your report, and it should provide you with the Net Revenue impact based on the measures from the two different tables.

Anonymous
Not applicable

Hi 123abc, thank you for responding so quickly.

The problem is that using this measure, PBI performs the calculation multiple times (for all customers/dates), instead of just once per product, and I get a wrong result in the visual:

LStoychev_5-1695462197993.png

 

 

I have calculated the same thing in Excel, and that is the result I am trying to achieve:

 

LStoychev_3-1695461752105.png

 

 

 

123abc
Community Champion
Community Champion

I see that you want to calculate the Price Delta and the Net Revenue impact per product and not aggregate it across all customers/dates. To achieve this, you need to use context transition functions in DAX to perform the calculations at the product level. You can modify your measures as follows:

Assuming you have a product identifier (let's call it "ProductID") in your tables, you can create the following measures:

  1. Price Delta per Product:

Price Delta per Product =
SUMX(
FILTER('Average of Contract price', 'Average of Contract price'[Account] = "Client D"),
'Average of Contract price'[AveragePrice2] - 'Average of Contract price'[AveragePrice1]
)

 

This measure calculates the Price Delta for each product separately by iterating over the 'Average of Contract price' table and applying a filter for "Client D."

  1. Total Quantity per Product for Client D (assuming you have a 'Volume' table with a relationship to your product table):

Total Quantity per Product Client D =
SUMX(
FILTER('Volume', 'Volume'[Customer] = "Client D"),
'Volume'[Volume]
)

 

This measure calculates the total quantity for Client D per product.

  1. Net Revenue Impact per Product:

Net Revenue Impact per Product = [Price Delta per Product] * [Total Quantity per Product Client D]

 

Now, when you use the "Net Revenue Impact per Product" measure in your visuals, it should correctly calculate the impact at the product level, as shown in your Excel example.

Anonymous
Not applicable

It is working now. Thank you so much!

123abc
Community Champion
Community Champion

You are always wellcome.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.