cancel
Showing results for
Did you mean:
Highlighted
Helper II

## Divide sum by group on average by group

Hi!

I have a dataset like this:

 id Order Sold 1 1400 700 1 1400 500 1 1400 150 2 1500 600 2 1500 600 2 1500 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
Community Support

Hi @Marit ,

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

Best Regards,
Kelly

5 REPLIES 5
Highlighted
Super User III

Hi @Marit ,

Try this measure:

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

Proud to be a Super User!

Highlighted
Helper II

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

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

Once added, you can then create below visual

Highlighted
Helper II

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
Community Support

Hi @Marit ,

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

Best Regards,
Kelly

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

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