Forum Discussion

JeroenN's avatar
JeroenN
Advocate I
10 years ago
Solved

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...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    10 years ago

    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.

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
10 years ago

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's avatar
JeroenN
Advocate I
10 years ago

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