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

Sumif in Power BI?

Hi!

I want to compare the score of an item in my database on a particular variable with the scores of the group of cities to which the city belongs. Which formula should I use in a new column? Or is there an easier way to do this? In Excel, I normally use the sumIf formula.

Thanks! Jeroen

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Sumif in Power BI?

@JeroenN - Here is what I did. I created the following measure in 'Attendance':

 

% Men = SUM([#men]) / SUMX(ALL('Attendance'),[#men])

 

'Attendance' is the city table with the attendance numbers. The other table is city groups (citygroups). They are related based upon citygroup columns.

 

I then created two column charts (see below). Is this what you are after? You can select a particular city and it visually displays the performance versus the overall city group.

 

citygroup.png


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Sumif in Power BI?

Light on details here but it sounds like what you want is something like SUMX. SUMX allows you to apply a filter to a table, allowing you to select only the items that you want. Most likely you would implement this as a measure such as:

 

= SUMX(FILTER(citytable[group]=[group]),[value])

 

Documentation for SUMX

https://support.office.com/en-US/article/SUMX-Function-DAX-9ca68d1f-34cd-4a98-bc5c-36646118811a

 

This being said, if you have a related "cities group" table and you simply create a measure:

 

= SUM([value])

 

You place that in a table along with the group, the table row should filter the context of the measure to just that group.

 

Tough to be more specific without sample data, tables, etc.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

JeroenN Frequent Visitor
Frequent Visitor

Re: Sumif in Power BI?

Thanks; I added the measure in the citiesgroup table.

That works fine.

But ... when I select one city in my visuals (% of men), the calculated score (the new measure) of the citygroup just shows the one selected city.

 

This is the setup of my data. Very simple.

 

Table 1:

city, #men, #women, citygroup

A; 102; 98; 3

B; 458; 465; 2

C; 58; 57; 1

D; 120; 132; 1

E; 458; 465; 2

..

 

Table 2:

 

citygroup, name

1 urban cities

2 rural cities

3 suburban cities

..

 

I'm new to Power BI, maybe my Excel-mind gets me off on the wrong foot.

Super User
Super User

Re: Sumif in Power BI?

@JeroenN - Here is what I did. I created the following measure in 'Attendance':

 

% Men = SUM([#men]) / SUMX(ALL('Attendance'),[#men])

 

'Attendance' is the city table with the attendance numbers. The other table is city groups (citygroups). They are related based upon citygroup columns.

 

I then created two column charts (see below). Is this what you are after? You can select a particular city and it visually displays the performance versus the overall city group.

 

citygroup.png


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

JeroenN Frequent Visitor
Frequent Visitor

Re: Sumif in Power BI?

Thanks for your input!

 

I added the FILTER to the SUMX - formula:

 

In the citygroup table (TableB) 

%MenGroup = sumx(filter(TableA;TableA[Reference]=TableB[Reference]);TableA[Men]) / (sumx(filter(TableA;TableA[Reference]=TableB[Reference]);TableA[Men]) + sumx(filter(TableA;TableA[Reference]=TableB[Reference]);TableA[Women])

 

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)