Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all, I am trying to create a measure that lets me calculate the total cost properly on the product category level.
I have managed to calculate the total cost for SKU level, but the subtotal/grandtotal isn't correct. The DAX formula I used is:
SUMX( SalesTable, SalesTable[Quantity] * RELATED(Product[Unit Cost]))
I think for the category level, the measure should look something like CALCULATE(SUM(SalesTable[Quantity]), FILTER(...)), but I don't know how to build it. Do I write "FILTER( SalesTable[SKU]=VALUES(Product[SKU])"?
How do I use the FILTER function correctly (if that is indeed the correct function to use)?
Solved! Go to Solution.
Your first measure should work for category too. Have you tested it?
Your first measure should work for category too. Have you tested it?
Thank you! I realised I made a silly error in the fields used.
Yes I've tested it, but for some reason there's a difference in the subtotal (and grand total) and when I highlight the individual entries.
Also, isn't SUMX calculation intensive?
@Anonymous,
Share us a simplified model, then we could have a test.
Hi all, I am trying to create a measure that lets me calculate the total cost properly on the product category level.
I have managed to calculate the total cost for SKU level, but the subtotal/grandtotal isn't correct. The DAX formula I used is:
SUMX( SalesTable, SalesTable[Quantity] * RELATED(Product[Unit Cost]))
I think for the category level, the measure should look something like CALCULATE(SUM(SalesTable[Quantity]), FILTER(...)), but I don't know how to build it. Do I write "FILTER( SalesTable[SKU]=VALUES(Product[SKU])"?
How do I use the FILTER function correctly (if that is indeed the correct function to use)?
Hi all, I am trying to create a measure that lets me calculate the total cost properly on the product category level.
I have managed to calculate the total cost for SKU level, but the subtotal/grandtotal isn't correct. The DAX formula I used is:
SUMX( SalesTable, SalesTable[Quantity] * RELATED(Product[Unit Cost]))
I think for the category level, the measure should look something like CALCULATE(SUM(SalesTable[Quantity]), FILTER(...)), but I don't know how to build it. Do I write "FILTER( SalesTable[SKU]=VALUES(Product[SKU])"?
How do I use the FILTER function correctly (if that is indeed the correct function to use)?
Can someone tell me if the info I provided is incomplete?
Hi John,
Are you able to provide the pbix file, your first query looks good and should work throughout but would need to see the model to get a better understanding of the problem.
Thanks,
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
50 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |