Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello, sorry if there are multiples, was not appearing in the topic list!
I'm trying to get a Dax calculation right for a measure and having some problems. I know it's some combination of allselected(), all() or allexcept() probably but I'm struggling!
Here is an example of the situation data:
Product Dimension Table:
Product Name - Product Category - Product Sub Category
Bike Sporting Goods Cycling
Helmet Sporting Goods Cycling
Soccer Ball Sporting Goods Soccer
Jacket Apparel Outerwear
Which would be related to the sales table:
Product Name (for reference) - Sales
Bike 100
Helmet 10
Soccer Ball 5
Jacket 25
What I'm trying to get is the Category and Sub Category Sales as measures for comparison, so it would be like below:
Product Name - Product Sales - Sub Category Sales - Category Sales
Bike 100 110 115
Helmet 10 110 115
Soccer Ball 5 5 115
Jacket 25 25 25
What I've been trying is:
Sub Category Sales=Calculate(sum(Sales[sales]),allselected('product'[sub category]) or allexcept() in place of allselected
Category Sales=Calculate(sum(Sales[sales]),allselected('product'[category]) or allexcept() in place of allselected
But it is only showing the product sales for all three, I'm sure I am close but just not understanding something with DAX filter context.
Thanks for any help!
Solved! Go to Solution.
Try this:
[SubCategory Sales] = VAR CurrentSubCategory = VALUES ( ProductTable[Product Subcategory] ) RETURN CALCULATE ( SUM ( Sales[Sales] ), ALL ( ProductTable[Product Name] ), ProductTable[Product Subcategory] = CurrentSubcategory )
and this:
[Category Sales] = VAR CurrentCategory = VALUES ( ProductTable[Product Category] ) RETURN CALCULATE ( SUM ( Sales[Sales] ), ALL ( ProductTable[Product Name] ), ProductTable[Product Category] = CurrentCategory )
Hi,
Why would you need something like this?
=SUM(Data[Sales])
This will create a visual with 2 columns.
Try this:
[SubCategory Sales] = VAR CurrentSubCategory = VALUES ( ProductTable[Product Subcategory] ) RETURN CALCULATE ( SUM ( Sales[Sales] ), ALL ( ProductTable[Product Name] ), ProductTable[Product Subcategory] = CurrentSubcategory )
and this:
[Category Sales] = VAR CurrentCategory = VALUES ( ProductTable[Product Category] ) RETURN CALCULATE ( SUM ( Sales[Sales] ), ALL ( ProductTable[Product Name] ), ProductTable[Product Category] = CurrentCategory )
Thanks for this! I think that is working!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |