Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Marit
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

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
camargos88
Community Champion
Community Champion

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!



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

@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

 




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



Proud to be a Super User!


Follow me on linkedin

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.

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.