Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 |
Solved! Go to 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.
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
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.
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
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
104 | |
103 | |
87 | |
61 |
User | Count |
---|---|
162 | |
132 | |
131 | |
95 | |
86 |