Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Group total with filters

Hi,

 

I have a dataset that looks like this.

 

ItemPeriodDCPRESERVESRATESLOANSCONTRIBUTIONSGRANTSOTHER
100113800000      
10024192640003799495     
1005122540000 0    
10088  0  1185057 
102114634105 1500000  1500000 
102135485408 1034999    
102553085987 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.

 

 

 

 

Group total

3 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

    try a measure

    Measure = CALCULATE(SUMX('Table',[DCP]+[RESERVES]+[RATES]+[LOANS]+[GRANTS]+[CONTRIBUTIONS]+[OTHER]), ALLEXCEPT('Table','Table'[Period]),'Table'[DCP]>0)
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        az38
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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