Forum Discussion
Helper IVSelect distinct to get cost
I am trying to create a measure to get the cost for each delivery. My table looks like:
| Deliver | Item | Delivery Cost |
| A | 1 | $1 |
| A | 2 | $1 |
| A | 3 | $1 |
| A | 4 | $1 |
| A | 5 | $1 |
| B | 6 | $20 |
| B | 7 | $20 |
| B | 8 | $5 |
| C | 9 | $5 |
The measure should show the cost as:
A = $1
B = $20
C=$5
4 Replies
TomSinAAHello, I have a table wit deliveries and items for each delivery along with a cost for each delivery. How can I create a measure to get the cost for each distinct delivery. My data looks like this:
Deliver Item Delivery Cost A 1 $1 A 2 $1 A 3 $1 A 4 $1 A 5 $1 B 6 $20 B 7 $20 B 8 $5 C 9 $5
So the measure for the cost for each delivery should be:
A = $1
B = $20
C = $5
- d_gosbell
Super User
One option would be to use a measure like the following
Total Delivery Cost = var _costs = SUMMARIZE('Table','Table'[Deliver],'Table'[Delivery Cost]) return sumx(_costs,[Delivery Cost])The other way of doing this would be to split the item level data into one table and the delivery based data into another table as it looks like you have data of mixed grain in this table which is what causes issues like this. This will complicate your data load and require you to build a proper star schema with dimension tables linked to each of the different fact tables, but it will simplify and speed up measures like this.
v-lid-msftCommunity Support
Hi TomSinAA ,
We can use the following measure in visual to meet your requirement, it will calculate the cost for different divery in visual.
Cost Measure = MAX('Table'[Delivery Cost])
If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Best regards,
