cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Divide sum by group on average by group

Hi!

 

I have a dataset like this:

 

idOrderSold
11400700
11400500
11400150
21500600
21500600
21500

300

 

I want calculated deliver% by calculate the sum of number of sold by id and then divide on the number of order of that id. Because the order is equal for each row of the id i tried to use the average of order by id. So i created three measures like this:

 

NSold= CALCULATE(SUM(Table[Sold]), Table[Id])

NOrder= CALCULATE(AVERAGE(Table[Order]), Table[Id])

Deliver%=DIVIDE([NSold], [NOrder])*100

 

However, I don't get any number for all the three measures. Would it be a problem if the id is missing in som colums? Do anyone have another solution on how to do this?

 

Regard Marit

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Hi @Marit ,

 

Could you pls provide some sample data which has date in it  with expected output?

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

View solution in original post

5 REPLIES 5
Highlighted
Super User III
Super User III

Hi @Marit ,

 

Try this measure:

_Delivery % = DIVIDE(SUM('Table'[Sold]), AVERAGE('Table'[Order]), 0) * 100

 

Capture.PNG 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Highlighted

Thank you for your suggestion. I don't get one row for each id by doing this. I think I have to specify that i should by one for each id in someway.

 

Regards Marit

Highlighted
Memorable Member
Memorable Member

@Marit You will have to create a index column in your data to be able to view data by each row. You can add index column in powequery window like below. 

 

negi007_1-1604301523035.png

 

Once added, you can then create below visual

 

negi007_0-1604301428095.png

 

Highlighted

Thank for your help!

I do not want to the delivery% per row in my data, I want to it per id. And then show the average per date in a visual. 

 

This meassure give med the right precentage in a table with id in the first coloumn:

Delivery%= DIVIDE(SUM(Table[Sold]), AVERAGE(Table[Order]), 0 )*100

But I can't use this meassure to get the average of these percentage by date in vissual.

Highlighted

Hi @Marit ,

 

Could you pls provide some sample data which has date in it  with expected output?

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors