Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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])
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |