Measure to calculate sum of (SKU * Unit Cost) on a Product Category Level?

[Anonymous]

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

[MattAllington · Accepted solution]

Your first measure should work for category too. Have you tested it?

[Anonymous]

Thank you! I realised I made a silly error in the fields used.

[Anonymous]

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?

[v-chuncz-msft]

@Anonymous,

Share us a simplified model, then we could have a test.

[Anonymous]

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

[Anonymous]

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

[Anonymous]

Can someone tell me if the info I provided is incomplete? 

[richbenmintz]

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,