Measure total ignoring filter

[Anonymous]

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.

Afzet =

CALCULATE(SUM(Facts[NettoAfzet]), FILTER(Calander, SUM(Forecast[dDemandKg]) > 0))

This returns the correct months, but my total is the grandtotal of sales of all months.

 

How do I fix my measure to display these totals correct for my sales aswel. Thanks up front.

[AlB · Accepted solution]

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 

[AlB · Accepted solution]

@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 

[AlB · Accepted solution]

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]

Hey AIB,

That worked great thanks. Any explenation as to why my old way yielded the wrong results?

It didn't occur to me that having the month num multiple times would affect the outcome since I had year selected in filter context outside the measure.   

Anyway, thanks again!

[negi007]

@Anonymous Have you tried using the Sumx function. Refert to this link

https://powerpivotpro.com/2012/03/subtotals-and-grand-totals-that-add-up-correctly/?nabm=0&utm_referrer=https%3A%2F%2Fwww.google.com%2F

Alternativly, will it be possible for you to share your powerbi file.