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.
we want to produce a dashboard that shows the category and subcategory of products that sold more than twice the average sales amount.
The folloiwng code produces the table :
BestCategories =
VAR Subcategories =
ALL ( 'Product'[Category], 'Product'[Subcategory] )
VAR AverageSales =
AVERAGEX (
Subcategories,
SUMX ( RELATEDTABLE ( Sales ), Sales[Quantity] * Sales[Net Price] )
)
VAR TopCategories =
FILTER (
Subcategories,
VAR SalesOfCategory =
SUMX ( RELATEDTABLE ( Sales ), Sales[Quantity] * Sales[Net Price] )
RETURN
SalesOfCategory >= AverageSales * 2
)
RETURN
TopCategories
However, is it possible to write a shorter and more efficient code using CALCULATE and Filter context ?
I have attached the Model View of the data set and the Out Put of the Code for reference.
Regards,
Somnath6309
Hi @somnath6309 ,
You can use the following DAX code:
BestCategories =
CALCULATETABLE (
SUMMARIZE (
Sales,
'Product'[Category],
'Product'[Subcategory],
"Total Sales", SUM ( Sales[Quantity] * Sales[Net Price] )
),
FILTER (
ALLSELECTED ( 'Product'[Category], 'Product'[Subcategory] ),
[Total Sales] >= 2 * AVERAGE ( Sales[Quantity] * Sales[Net Price] )
)
)
Hope it helps.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
108 | |
106 | |
86 | |
77 | |
69 |
User | Count |
---|---|
124 | |
112 | |
94 | |
84 | |
75 |