Calculate the percentage of sales

[Annzi]

Hi!

I'm a pretty new user to Power BI. Tried to find this in the forum. Would like some helps please.

I have an export from the sales system. I need to calculate the percentage on how we deliver.

I would like to add a measurement with DAX for this.

The issue is that the export gives me several rows for one article in one delivery because we pick it up more than once sometimes.

If the OrderedAmount is 100 and we pick it up 3 times we get 3 rows but the AmountOrdered is the same, not 1/3.

The exported data looks like below.

Expected result.

Order 1111 is 80%

Order 2222 is 100% 

Day 2024-02-12 is 75% (order 3333 not delivered in time)

Article 22 is 81,8%

Article 33 is 50%

Order	Article	AmountOrdered	AddedToOrder	OrderDone	OrderPickUpTime
1111	22	100	50	2024-02-12 12:00	2024-02-12 19:00
1111	22	100	20	2024-02-12 14:00	2024-02-12 19:00
1111	22	100	10	2024-02-12 16:00	2024-02-12 19:00
2222	22	10	10	2024-02-12 16:00	2024-02-12 21:00
2222	33	10	10	2024-02-12 16:00	2024-02-12 21:00
3333	33	10	10	2024-02-13 17:00	2024-02-12 21:00

[Annzi]

Hi @v-stephen-msft!

Thank you for your reply. Much appreciated. My first post so I've been checking every day :-).

Thank you for the tip to write out the formula. You didn't calculate the way I tried to explain

The code is correct regarding Order and Article but not Percentage when there is several Article.

I could have chosen other numbers to make it more clear.

Order 1111. 1 Article is ordered, but picked up 3 times. (50+20+10) / 100 = 80%. The code shows 80% 👍

Order 2222. 2 Article is ordered, each 10. (10+10) / (10+10) = 100%

The percentage for 1111 and 2222 is (50+20+10+10+10) / (100 +10 +10) = 83,3. The code shows 90,91.

If I choose Order 1111 and 2222 and only Article 22. (50+20+10+10) / (100+10) = 81,82. The code shows 81,82.

So almost there.

If you have a solution I would be grateful!

Kind regards Ann

[v-stephen-msft · Accepted solution]

Hi @Annzi ,

Thanks for your detailed explanation. Now I completely understand your requirement. I modified the [Percentage] measure:

Percentage = 
var _table=SUMMARIZE('Table','Table'[Order],'Table'[Article],'Table'[AmountOrdered]) RETURN
SUM('Table'[AddedToOrder])/SUMX(_table,[AmountOrdered])

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[Annzi]

Thank you so much @v-stephen-msft !

Spot on!

I'm so relieved.

I tested with my report with real data.

Have a great weekend!

Kind regards Ann Zimmerman

[v-stephen-msft]

Hi @Annzi ,

When I was browsing through the posts, I noticed that no one had replied to your post yet. Here I took a look at your requirements, and I found that if your logic is according to my understanding, it should result as follows:

If I don't understand it correctly, it's better to write out the formula for calculating the percentage result like me, thank you. If I understand correctly, here's the formula:

Percentage = 
var _table=SUMMARIZE('Table','Table'[Order],'Table'[AmountOrdered]) RETURN
SUM('Table'[AddedToOrder])/SUMX(_table,[AmountOrdered])

You can check the attachment for more details.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.