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

[AldoJavier26]

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
Affiliation	Client	Year	Month	Revenue
A	APPLE	2017	April - 2017	100
A	APPLE	2018	May - 2017	200
B	APPLE	2017	April - 2017	300
B	APPLE	2018	May - 2017	400
Table 2
Affiliation	Client	2017	2018
A	APPLE	100	200
B	APPLE	300	400

[mwegener]

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.

[az38 · Accepted solution]

@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

[AldoJavier26]

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.