Forum Discussion
Advocate ISumif 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
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.
4 Replies
- Greg_Deckler
Community Champion
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.
- 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
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])
