Hi all,
I’ve been batting away with trying to calculate the sales % of another specific category using DAX.
I have 20 product categories with Category A being out main Category. For all other categories I want to calculate their sales as a % of Category A sales. In excel es per below this is no issue, just need to do this in Power BI in order to have it dynamic by customer etc.
Update - I have found the solution, straight forward in the end. It was the subtle nuances of DAX tripping me up. I was missing the sort column as part of the ALL argument when calculating the sales for my main category. FT (shown as Category 1 in the attached excel).
I am still open to other suggestions if the below solution doesn’t look quite right.
CAT $ Ratio of FT =
VAR FT = CALCULATE([Sales $], ALL(V_DIM_PRODUCT[Master Category], V_DIM_PRODUCT[MCat Sort]), V_DIM_PRODUCT[Master Category] = “Frame and Truss”)
VAR BH = [Sales $]
VAR result = “$ Ratio of F&T” & " “& FORMAT(DIVIDE(bh, ft, 0),”$#0.00")
RETURN
result
Category Sales.xlsx (11.4 KB)
Summary
This text will be hidden