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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
adriansuteu
Advocate I
Advocate I

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

View solution in original post

4 REPLIES 4
DenysDnepr
Advocate I
Advocate I

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.

 

instead, Try the only measure:

 

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

@DenysDnepr 

 

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

SUMX(ADDCOLUMNS(PriceList,"new column",Qty[Measure]*PriceList[Retailer]),[new column])Capture.JPG

 

and image shows my tables 

Vvelarde
Community Champion
Community Champion

@adriansuteu

 

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

Helpful resources

Announcements
Europe Fabric Conference

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.