'Power BI Grouping

Location Won Lost
Home 3 4
Home 2 3
Home 5 4
Home 4 6
Away 3 4
Away 2 3
Away 5 4
Away 4 6

This is an example table that I have in Power BI. I am trying to create a new table, that groups by Location, sums up the values of won and lost, and adds a third column that has the ratio of sum(won)/sum(lost) called Ratio. So all in all, you have three columns: Location (either home or away), Wins (sum of wins), Lost (sum of lost), ratio (the result of dividing wins and lost, the previous two columns)

How can I write the formula I need in Power BI?



Solution 1:[1]

There is a function for that called SUMMARIZE, see docs

May be like:

SUMMARIZE(your_table  
      , your_table[Location]  
      , "Total Won", SUM(your_table[Won])  
      , "Total Lost", SUM(your_table[Lost])   
      , "Ratio", SUM(your_table[Won]/your_table[Lost])
      )

Solution 2:[2]

You can also create this using Power Query.

  • Your existing table is produce by a query named myTable
  • In Power BI Desktop, select `Queries => Transform"
  • In the PQ UI, select Home=>Advanced Editor and paste the code below into the window that opens.
    • You may need to change the name in the second line from myTable to whatever that query is really named
  • I use the Table.Group function to do the appropriate calculations on each grouped sub-table

M Code

let
    Source = myTable,

//Group by Location, then add the columns
    #"Grouped Rows" = Table.Group(myTable, {"Location"}, {
        {"Wins", each List.Sum([Won]), type nullable number}, 
        {"Losses", each List.Sum([Lost]), type nullable number},
        {"Ratio", each List.Sum([Won])/List.Sum([Lost]), type number}
        })
in
    #"Grouped Rows"

Results from your data above
enter image description here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 luisvenezian
Solution 2 Ron Rosenfeld