Group total with filters

[Anonymous]

Hi,

I have a dataset that looks like this.

Item	Period	DCP	RESERVES	RATES	LOANS	CONTRIBUTIONS	GRANTS	OTHER
1001	1	3800000
1002	4	19264000	3799495
1005	12	2540000		0
1008	8			0			1185057
1021	1	4634105		1500000			1500000
1021	3	5485408		1034999
1025	5	3085987		265190			2020000

I'm trying to calculate the total of (DCP + RESERVES + RATES + LOANS + CONTRIBUTIONS + GRANTS + OTHER) for each Period

only for the items (not rows) that the sum of DCP is greater than 0.

Any thoughts?

Thanks in advance.

[az38]

Hi @Anonymous 

try a measure

Measure = CALCULATE(SUMX('Table',[DCP]+[RESERVES]+[RATES]+[LOANS]+[GRANTS]+[CONTRIBUTIONS]+[OTHER]), ALLEXCEPT('Table','Table'[Period]),'Table'[DCP]>0)

[Anonymous]

Hi @az38 ,

I tried that and it only gives the sum of rows where DCP is greater than 0. What I'm trying to achieve is to get the total of items (for each period) if an item has a value in the DCP column for any period.

Hope this makes sense.

Thanks

[az38]

Hi @Anonymous 

not sure I understand you well but try

Measure = 
var _isDCP = CALCULATE(COUNTROWS(Table), ALLEXCEPT('Table','Table'[Period]),'Table'[DCP]>0)
RETURN
if(_isDCP > 1,
CALCULATE(SUMX('Table',[DCP]+[RESERVES]+[RATES]+[LOANS]+[GRANTS]+[CONTRIBUTIONS]+[OTHER]), ALLEXCEPT('Table','Table'[Period])),
BLANK()
)

if it will not work as you need, please show a desired output based on your dummy data