cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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.

 

 

 

 

3 REPLIES 3
Highlighted
Super User II
Super User II

Re: Group total with filters

Hi @afernandocasey 

try a measure

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Highlighted
Frequent Visitor

Re: Group total with filters

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

Highlighted
Super User II
Super User II

Re: Group total with filters

Hi @afernandocasey 

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

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors