Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
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
Raj
Hi @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.
Hi,
You may download my PBI file from here.
This works!
However, I just realized that the same same order numbers appear for two different categories. Using the solution in this instance no longer works.
For example I have the below:
Date | Game | Order | Users | Category |
1/1/2018 | A | 0 | 100 | Red |
1/1/2018 | B | 3 | 90 | Blue |
1/1/2018 | C | 6 | 50 | Blue |
1/2/2018 | A | 0 | 100 | Blue |
1/2/2018 | B | 3 | 90 | Red |
1/2/2018 | C | 6 | 50 | Red |
1/3/2018 | A | 0 | 100 | Blue |
1/3/2018 | B | 3 | 90 | Red |
1/3/2018 | C | 6 | 50 | Blue |
I attempted to have the CUSTOMER MIN ORDER be calculated based on the category filter selected by the user, but it's throwing out an error in the visual:
Customers at min order = CALCULATE([Number of users],FILTER(ALL(Data[Game]),Data[Game]=LOOKUPVALUE(Data[Game],Data[Order],CALCULATE([Min order],ALL(Data[Game]), FILTER(Data, SELECTEDVALUE(Data[Category])))))
Disregard my post above. I simply created a new column that differentiated the order numbers between the categories. Your solution now works.
Thank you so much for the help!
You are welcome.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |