cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

@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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Frequent Visitor

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.

@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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




View solution in original post

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])

 

 

Super User IV
Super User IV

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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors