Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have a table with the following data:
The table calculates the total forecasted amount for each month (all years combined so not for a seperate year).
I would like to have a measure that takes the value of the current month (so in this case May), but in such a way that even if I set a filter on current year only, that it still takes all the years together.
So now I would like to have the value 329, but the value should be consistent so that if I select a filter on a visual for example: is in this year that it still shows the combined value of all years together which is 329 and not filtered on this year.
I hope my explanation is clear. If not let me know and I'll try to explain it better.
Solved! Go to Solution.
Hi @Niels_T ,
If you want to remove the filter of year in sum of Forecast, you can try ALL or ALLEXCEPT function.
Here I use ALLEXCEPT function, as below, sum of Forecast will be only filtered by month, not by year. If this month is may it will give you sum of may = 329.
Forecasted Amount =
VAR percentage =
DIVIDE (
SUM ( 'Credit Memo + Invoice'[Amount] ),
CALCULATE ( SUM ( 'Credit Memo + Invoice'[Amount] ), ALLSELECTED () )
)
RETURN
CALCULATE (
SUM ( 'Forecast'[Forecast] ),
ALLEXCEPT ( 'Forecast', 'Forecast'[Month] )
) * percentage
If this reply still couldn't help you solve your problem, please share a sample file with me and show me the result you want. This will make it easier for me to find the solution.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Can you give an example of what your end result should look like?
The end result is used for a gauge:
In the gauge I have YTD amount and the total amount we have to reach at the end of this year.
The target value for the gauge should be 329 as it is May.
A seperate function is then needed because I use a filter on this gauge to take the forecasted amount for this year.
Try using "ALL" in the measure you will create to grab the value for May (or any other month). This will ignore the filter for this year.
Could you show me how this can be added to the measure?
Forecasted Amount = VAR percentage = Divide(SUM('Credit Memo + Invoice'[Amount]), calculate(SUM('Credit Memo + Invoice'[Amount]), allselected()) ) RETURN
SUM('Forecast'[Forecast])* percentage
Hi @Niels_T ,
If you want to remove the filter of year in sum of Forecast, you can try ALL or ALLEXCEPT function.
Here I use ALLEXCEPT function, as below, sum of Forecast will be only filtered by month, not by year. If this month is may it will give you sum of may = 329.
Forecasted Amount =
VAR percentage =
DIVIDE (
SUM ( 'Credit Memo + Invoice'[Amount] ),
CALCULATE ( SUM ( 'Credit Memo + Invoice'[Amount] ), ALLSELECTED () )
)
RETURN
CALCULATE (
SUM ( 'Forecast'[Forecast] ),
ALLEXCEPT ( 'Forecast', 'Forecast'[Month] )
) * percentage
If this reply still couldn't help you solve your problem, please share a sample file with me and show me the result you want. This will make it easier for me to find the solution.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
81 | |
70 | |
49 |
User | Count |
---|---|
143 | |
124 | |
107 | |
60 | |
55 |