Divide sum by group on average by group

[Marit]

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

[camargos88]

Hi @Marit ,

Try this measure:

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

 

[Marit]

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

[negi007]

@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

[Marit]

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.

[v-kelly-msft · Accepted solution]

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!