Forum Discussion

JeroenN's avatar
JeroenN
Icon for Advocate I rankAdvocate 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
Icon for Community Champion rankCommunity Champion
10 years ago

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.