Forum Discussion
Measure total ignoring filter
Hello I've got 3 tables
Table A = Facts has sales
Table B = Forecast has forecasts
Table C = Calander table
Sales are indivdual, Forecasts are monthly first of the month, and Calander is date.
They are linked by the calander table 1 too many
Maand = month, Netto Afzet = sales, Netto Forecast = forecast
I got this table and what i want is this.
But this is done with manual filter, and i want these totals in a card. I also need them for other calculations.
So i made 2 measures. 1 for the forast and 1 for the sales. I only want the rows that have forecasts.
How do I fix my measure to display these totals correct for my sales aswel. Thanks up front.
Hi Anonymous
Afzet V2 = SUMX ( FILTER ( DISTINCT ( Calander[Maand] ), CALCULATE ( SUM ( Forecast[dDemandKg] ) ) > 0 ), CALCULATE ( SUM ( Facts[NettoAfzet] ) ) )If you have more than one field (Maand) in the rows of the table visual, you might have to add it to the first argument of FILTER( )
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Anonymous
I'd need to see the complete model to give a fully accurate answer but:
Your code works well at the individual rows of the visual (not total) because filter context restricts the SUM( ) to the current month. At the total, however, you have no month in the filter context and therefore the SUM(Forecast...) is done for the full year (since you have that selected). That is > 0 and therefore SUM(Facts..) is run over the whole year
The provided solution basically reenacts step by step what happens in the visual. At the total, it checks, month by month, whether the forecast demand is > 0 and if so adds up the SUM(Facts...) for that month. Note the important role context transition plays here. At the individual rows, it does the same but restricted to the current month in the filter context.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
4 Replies
Hi Anonymous
Afzet V2 = SUMX ( FILTER ( DISTINCT ( Calander[Maand] ), CALCULATE ( SUM ( Forecast[dDemandKg] ) ) > 0 ), CALCULATE ( SUM ( Facts[NettoAfzet] ) ) )If you have more than one field (Maand) in the rows of the table visual, you might have to add it to the first argument of FILTER( )
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Anonymous
I'd need to see the complete model to give a fully accurate answer but:
Your code works well at the individual rows of the visual (not total) because filter context restricts the SUM( ) to the current month. At the total, however, you have no month in the filter context and therefore the SUM(Forecast...) is done for the full year (since you have that selected). That is > 0 and therefore SUM(Facts..) is run over the whole year
The provided solution basically reenacts step by step what happens in the visual. At the total, it checks, month by month, whether the forecast demand is > 0 and if so adds up the SUM(Facts...) for that month. Note the important role context transition plays here. At the individual rows, it does the same but restricted to the current month in the filter context.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Anonymous Have you tried using the Sumx function. Refert to this link
Alternativly, will it be possible for you to share your powerbi file.
