Sumif in Power BI?

[JeroenN]

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

[JeroenN]

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.

[Greg_Deckler · 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.

[JeroenN]

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

[Greg_Deckler]

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.