cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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:

 

GameOrderUsersCompletion %
A0100 
B39090.00%
C68080.00%
D97070.00%
E125050.00%
F152525.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

Accepted Solutions
Highlighted
Super User V
Super User V

Re: Divide based on first row

Hi,

 

You may download my PBI file from here.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
Highlighted
Resident Rockstar
Resident Rockstar

Re: Divide based on first row

Hi @NoobToPro

 

 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

Highlighted
Frequent Visitor

Re: Divide based on first row

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:

 

DateGameOrderUsers
1/1/2018A0100
1/1/2018B390
1/1/2018C650
1/2/2018A0100
1/2/2018B390
1/2/2018C650
1/3/2018A0100
1/3/2018B390
1/3/2018C650

 

And here is the result I would like based on the backend in a table visual:

 

GameOrderUsersCompletion %
A0300 
B327090.00%
C615050.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. 

Highlighted
Super User V
Super User V

Re: Divide based on first row

Hi,

 

You may download my PBI file from here.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Highlighted
Frequent Visitor

Re: Divide based on first row

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

 

DateGameOrderUsersCategory
1/1/2018A0100Red
1/1/2018B390Blue
1/1/2018C650Blue
1/2/2018A0100Blue
1/2/2018B390Red
1/2/2018C650Red
1/3/2018A0100Blue
1/3/2018B390Red
1/3/2018C650Blue

 

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. 
Highlighted
Frequent Visitor

Re: Divide based on first row

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! 

Highlighted
Super User V
Super User V

Re: Divide based on first row

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors