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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Annzi
Frequent Visitor

Calculate the percentage of sales

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%

 

 

OrderArticleAmountOrderedAddedToOrderOrderDoneOrderPickUpTime
111122100502024-02-12 12:002024-02-12 19:00
111122100202024-02-12 14:002024-02-12 19:00
111122100102024-02-12 16:002024-02-12 19:00
22222210102024-02-12 16:002024-02-12 21:00
22223310102024-02-12 16:002024-02-12 21:00
33333310102024-02-13 17:002024-02-12 21:00
1 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])

vstephenmsft_0-1708655632977.png

vstephenmsft_1-1708655644333.pngvstephenmsft_2-1708655649680.png

 

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.           

View solution in original post

4 REPLIES 4
Annzi
Frequent Visitor

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])

vstephenmsft_0-1708655632977.png

vstephenmsft_1-1708655644333.pngvstephenmsft_2-1708655649680.png

 

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

v-stephen-msft
Community Support
Community Support

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:

vstephenmsft_0-1708593633408.png

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])

vstephenmsft_1-1708593825972.png

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.           

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors