cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## 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.

1 ACCEPTED SOLUTION
Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
6 REPLIES 6
Super User

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

Anonymous
Not applicable

Hi @rajendran,

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.

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

@Ashish_Mathur

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])))))`
I should've made my original posts more clear. Thanks for any assistance.
Anonymous
Not applicable

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!

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements