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

Recreating SUMIFS formula with Calculate and FILTERS, so that conditions can equal values in rows

Hi ,

 

I'm trying to create a new column in Table 2 in Power BI (example below already has the columns..), by taking the Revenue from the Details table, by not using a Measure. I have way too many Clients to write a condition for every client, so need a IF/FILTER or another function to serve this purpose similar to a SUMIFS in excel. So that if my table changes clients/affiliation, the revenue would adjust with it.

 

This is the current formula I have, but it obviously doesn't work.

2017A = CALCULATE(

sum('Details'[Revenue]),

FILTER('Details','Details'[Affiliation] = Table 2[Affiliation] ),

FILTER('Details', 'Details'[Client] = Table 2[Client] ),

FILTER('Details', 'Details'[Month] = "April - 2017" )

)

 

This is an example of the tables I have.

 

Details
    
AffiliationClientYearMonthRevenue
AAPPLE2017April - 2017100
AAPPLE2018

May - 2017

200
BAPPLE2017April - 2017300
BAPPLE2018May - 2017400
     
Table 2    
AffiliationClient20172018 
AAPPLE100200 
BAPPLE300400 
1 ACCEPTED SOLUTION
az38
Super User
Super User

@AldoJavier26 

why dont you want to use a measures?

anyway in your technique correct syntax should look like

2017A = CALCULATE(
sum('Details'[Revenue]),
FILTER(ALL('Details'),'Details'[Affiliation] = Table 2[Affiliation] && 'Details'[Client] = Table 2[Client] && 'Details'[Month] = "April - 2017" )
)

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


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

View solution in original post

3 REPLIES 3
mwegener
Super User
Super User

Hi @AldoJavier26 ,

 

you could create a calculated table like this:

Table 2 = SUMMARIZE(Details,Details[Affiliation],Details[Client],"2017",CALCULATE(SUM(Details[Revenue]),Details[Year]=2017),"2018",CALCULATE(SUM(Details[Revenue]),Details[Year]=2018))

 Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


az38
Super User
Super User

@AldoJavier26 

why dont you want to use a measures?

anyway in your technique correct syntax should look like

2017A = CALCULATE(
sum('Details'[Revenue]),
FILTER(ALL('Details'),'Details'[Affiliation] = Table 2[Affiliation] && 'Details'[Client] = Table 2[Client] && 'Details'[Month] = "April - 2017" )
)

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


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

View solution in original post

Well,  went down the path of creating measures with  IF functions, but found myself not being able to to "pivot" or create filtered visualizations off of these measures.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.