Divide based on first row

[Anonymous]

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. 

[Anonymous]

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]

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. 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

[Anonymous]

@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]

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! 

[Ashish_Mathur]

You are welcome.