Advanced Totals: Only sum up current turnover of a year
Hey guys! Is it possible to only sum up the turnover of a current year? (Advanced Totals)
In my discovery I've got two data types in the columns. One for the current year (2024), one for the year before (2023). I would only like to calculate the turnover of 2024 in a column sum.
2 replies
-
Hi
When you have different Measures in your grid, you can change the totals logic and/or suppress totals for specific Measures. However, we can't do that when it is the same Measure, but a different series, i.e. sale for 2023 and 2024. So we'll have to do it manually.
Basically, we need to perform the totals calculation when it is 2023 and suppress it when it is not 2023. to do this we need to examine the caption of each of our columns (Years) and if it is 2023 aggregate whatever is on the rows, otherwise do nothing.
This can be achieved using the "CurrentMember" functions with an IF statement. In pseudocode, if the the column is labelled 2023, then aggregate all the countries, otherwise do nothing.
Note that the calculation is saved into the Country dimension.
Add that new custom member into the grid and there you go!
Hope that helps.
Ian
-
Thank you very for the quick answer! Now it works.
Kind Regards,
Jona