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

## Calculate Total Sales Value while taking the prices/product from a different table

I have the following tables:

Sales with column ProductID, among others

Products with columns ProductID and Price among others.

I need to create a visual that will take the Revenue.

My question is how do I take the Price from the related table?

The same product can appear multiple times in the Sales table and I need to sum this Revenue.

1 ACCEPTED SOLUTION

Didn't work for me.

But I figured it out by creating a new column in the Orders table

RevenuePerSale = 'Sales'[Quantity] * related(Products[Item Price])

And then the desired measure: Revenue (\$) = sum(Sales[RevenuePerSale])

4 REPLIES 4

It's ok to create NEW column, when you don't have much data.

In case you have a lot of data, it will increase storage volume, and, perhaps, slow down calculations.

Revenue = sumx( addcolumns( 'Sales'; "new column"; 'Sales'[Quantity] * related(Products[Item Price])); [new column])

Helper IV

Mesaure seems to work well in a table with small dataste. However it takes ages in matrix.

Is there another way which maybe works fatser.

This is the measure I'm using as per your suggetion

and image shows my tables

Community Champion

hi

use this measure:

Revenue = Sum(Sales[Units])*sum('Product'[Price])

And in a visual insert Product (from Product Table) and Revenue.

Lima - Peru

Didn't work for me.

But I figured it out by creating a new column in the Orders table

RevenuePerSale = 'Sales'[Quantity] * related(Products[Item Price])

And then the desired measure: Revenue (\$) = sum(Sales[RevenuePerSale])

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 - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors