DAX for calculated column that shows relative value compared to the parent in a tabular model

Question

I have a dimension table

ProductID Product_Name Sub-Category Category

There is a hierarchy Product-Name-> Sub-Category -> Category

I also have a fact table with sales Data and the ProductID.

I would like to have a calculated column that shows me the share of a product in its sub category for the selected measure. Something like "measure for Product-Name divided by measure in Sub-Category" and the value in the column would be 5% for example.

How do I solve this in a tabular modal?

Answer 1

Try this:

Calc% = 
var __SubCategorySUM = CALCULATE(SUM([Sales]), FILTER(ALL('YourTable'),  
SELECTEDVALUE('YourTable'[Sub-Category]) = 'YourTable'[Sub-Category]))
var __SumProd = CALCULATE(SUM[Sales])

return

DIVIDE(__SumProd , __SubCategorySUM )