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.
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!
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |