Forum Discussion
Divide based on first row
How would I go about creating a measure that divides each row's value by the very first row based on order numbers?
My data looks like this:
| Game | Order | Users | Completion % |
| A | 0 | 100 | |
| B | 3 | 90 | 90.00% |
| C | 6 | 80 | 80.00% |
| D | 9 | 70 | 70.00% |
| E | 12 | 50 | 50.00% |
| F | 15 | 25 | 25.00% |
What I'm trying to get is the completion % in my table. In Excel, you would just have divide all user values from games B-F by A's user value to get the completion %. However, I'm strugging to figure out how to do so in Power BI.
Any help would be appreciated. Thank you.
6 Replies
Hi Anonymous
How do you decide the firts row? Game "A" is the first row always?
If thats the case,
Completion = SUM(Table[Users] )/ CALCULATE ( SUM(Table[Users]), Filter ( Table, Table[Game]= "A")
OR
Completion = DIVIDE( SUM(Table[Users] ),
CALCULATE ( SUM(Table[Users]), Filter ( Table, Table[Game]= "A")
)
Thanks
RajHi Anonymous,
Thanks for the assistance. When I tried your measure I was getting the same percentage across all of the rows. I had a brain fart and realized that the data table I presented in the original post was a summarized table visual and not the actual backend data.
How the backend table looks like:
Date Game Order Users 1/1/2018 A 0 100 1/1/2018 B 3 90 1/1/2018 C 6 50 1/2/2018 A 0 100 1/2/2018 B 3 90 1/2/2018 C 6 50 1/3/2018 A 0 100 1/3/2018 B 3 90 1/3/2018 C 6 50 And here is the result I would like based on the backend in a table visual:
Game Order Users Completion % A 0 300 B 3 270 90.00% C 6 150 50.00% What I'm trying to achieve is getting the sum of users for all the games and then dividing those totals based on Game A, which has order 0. Order 0 will always be the first row.
Apologies for my lack of clarity.