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,
I user matrix to calculate the sum of towns sales by year.
My dataset is :
Année | Libelle | Mois num | Mois Lib | Montant HT |
2018 | Poitiers | 1 | Janvier | 50 |
2018 | Poitiers | 1 | Janvier | 100 |
2018 | Poitiers | 1 | Janvier | 200 |
2018 | Poitiers | 2 | Février | 50 |
2018 | Poitiers | 2 | Février | 150 |
2018 | Poitiers | 2 | Février | 256 |
… | ||||
2018 | Poitiers | 11 | Novembre | 126 |
2018 | Poitiers | 11 | Novembre | 147 |
2018 | Poitiers | 11 | Novembre | 644 |
2018 | Poitiers | 12 | Décembre | 456 |
2018 | Poitiers | 12 | Décembre | 236 |
2018 | Poitiers | 12 | Décembre | 654 |
2019 | Poitiers | 1 | Janvier | 50 |
2019 | Poitiers | 1 | Janvier | 100 |
2019 | Poitiers | 1 | Janvier | 200 |
… | ||||
2019 | Poitiers | 6 | Juin | 50 |
2019 | Poitiers | 6 | Juin | 100 |
2019 | Poitiers | 6 | Juin | 200 |
I created 2 measures to calcultate the sum of sales :
CA_2018 = CALCULATE( SUM(Data[Montant HT]); FILTER(Data;Data[Année ]=MIN(Data[Année ]) ) )
To calculate the sum of sales of min year (2018).
CA_2019_1 = IF (HASONEVALUE(Data_test[Année ]); BLANK(); CALCULATE( SUM(Data_test[Montant HT]); FILTER(Data_test;Data_test[Année ]=MAX(Data_test[Année ]) ) ))
To calculate the sum of sales of max year 2019.
I created a slicer to dynamically filter months lib.
But if i modify slicer selection, there is an update of 2018 calculate.
I would like that 2018 calculation not to be affected.
If i add the year with month, that's ok but i prefer don't use this solution.
Thanks
Yvon
Solved! Go to Solution.
Hi @Anonymous ,
You can use the following DAX to create measure that are not effected by month slicer, but the Name Column in table visual will still effected by the month. But the value of 2018 is always the full year.
SUM_2018 = SUMX ( FILTER ( ALL ( 'Table' ), AND ( 'Table'[Year] = MINX ( ALL ( 'Table' ), [Year] ), [Name] = SELECTEDVALUE ( 'Table'[Name] ) ) ), [Value] )
If you want to see all the name in this table always, just turn off the effect by this slicer.
BTW, pbix as attached.
Best regards,
Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more
Hi @Anonymous ,
You can use the following DAX to create measure that are not effected by month slicer, but the Name Column in table visual will still effected by the month. But the value of 2018 is always the full year.
SUM_2018 = SUMX ( FILTER ( ALL ( 'Table' ), AND ( 'Table'[Year] = MINX ( ALL ( 'Table' ), [Year] ), [Name] = SELECTEDVALUE ( 'Table'[Name] ) ) ), [Value] )
If you want to see all the name in this table always, just turn off the effect by this slicer.
BTW, pbix as attached.
Best regards,
Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more
Thanks
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |