Caluculate sum with 2 filters

[PBINewbie920]

Hi! 

I'm trying to create a measure like this: In this situation I want it to show the total value, regardless of the month (or any date filters on the page) and show for each location. This issue im having is with the "total" field:

I've tried a few different equations and can only get it to work when I add "min" and "max, but that obviously created problems with the totals (with min it takes the first value, with max it takes the last) Is there a way to get this equation to work without min/max or to get the total to actually add up?

Thanks!!

Formula1 =

CALCULATE (

SUM ( 'ALL Sources'[Total $] ),

FILTER (

ALL ( 'All Sources' ), 'ALL Sources'[Date] &&

'ALL Sources'[Location] = MAX ( 'ALL Sources'[Location] )

)

)

Formula2 =

CALCULATE (

SUM ( 'ALL Sources'[Total $] ),

FILTER (

ALL ( 'All Sources' ), 'ALL Sources'[Date] &&

'ALL Sources'[Location] = MIN ( 'ALL Sources'[Location] )

)

)

[Anonymous · Accepted solution]

HI @PBINewbie920,

You can try to use the following measure formula to calculate the summary values based on 'location' and 'date' groups:

Formula1 =
CALCULATE (
    SUM ( 'ALL Sources'[Total $] ),
    ALLSELECTED ( 'All Sources' ),
    VALUES ( 'ALL Sources'[Date] ),
    VALUES ( 'ALL Sources'[Location] )
)

Regards,

Xiaoxin Sheng

[amitchandak]

@PBINewbie920 , try measures like

Grand Total =
CALCULATE (
SUM ( 'ALL Sources'[Total $] ),
FILTER (
ALL ( 'All Sources' )
))

Location wise total =
CALCULATE (
SUM ( 'ALL Sources'[Total $] ),
FILTER (
ALL ( 'All Sources' ),
'ALL Sources'[Location] = MAX ( 'ALL Sources'[Location] )
)
)

or

Grand Total =
CALCULATE (
SUM ( 'ALL Sources'[Total $] ),
FILTER (
ALLSELECTED ( 'All Sources' )
))

Location wise total =
CALCULATE (
SUM ( 'ALL Sources'[Total $] ),
FILTER (
ALLSELECTED ( 'All Sources' ),
'ALL Sources'[Location] = MAX ( 'ALL Sources'[Location] )
)
)