Power BI Grouping

Question

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?

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

Answer 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