Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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])
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])
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
hi
use this measure:
Revenue = Sum(Sales[Units])*sum('Product'[Price])
And in a visual insert Product (from Product Table) and Revenue.
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])
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
73 | |
65 |