'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
- You may need to change the name in the second line from
- 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"
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 |