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.
I have two slicers site and period(Year, month) and a matrix having categories, sub categories as rows and sales as values. Now I need to create a dax where it shows total year sales of sub categories based on a month selection. If the user clicks on April under 2020 for one of the site, the Dax should show complete sales of the sub categories for that month year. If June 2020 is selected, the value should be same. The value should change only when a month from another year is selected and when site is changed.
Solved! Go to Solution.
Hi @Prabha45 ,
According to your description, you need to calculate the annual sales of the subcategories based on the selection of the slicer, and ignore the influence of the month.
You can try this measure:
SALES = CALCULATE(SUM(SALES[Amount]),FILTER(ALL(SALES),SALES[DATE].[Year] = MAX(SALES[Product].[Year])))
If this is not what you need, please provide sample data to address further.
Best Regards,
Gallen Luo
Hi @Prabha45 ,
According to your description, you need to calculate the annual sales of the subcategories based on the selection of the slicer, and ignore the influence of the month.
You can try this measure:
SALES = CALCULATE(SUM(SALES[Amount]),FILTER(ALL(SALES),SALES[DATE].[Year] = MAX(SALES[Product].[Year])))
If this is not what you need, please provide sample data to address further.
Best Regards,
Gallen Luo
Thank you that worked
Hi @Prabha45 ,
Are you using a separate date table?
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Yes, having a date table. Both the tables having many to one relationship
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |