Forum Discussion
Advocate ISumif 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.
Advocate IThanks; 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
Community Champion
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])