cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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:

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

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.

1 ACCEPTED SOLUTION
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.

8 REPLIES 8
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.

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.

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:

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

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!

Community Champion

You are always wellcome.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors