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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
sac_nav
Regular Visitor

I'm new to Power BI and need help with figuring out how to solve this problem! Sales Calculation!

I'm trying to do a sales calculation and I can't seem to figure out a solution. I need your help please!

I'm trying to calculate the number of devices sold for each product and not the number of products sold

 

Product 1 uses devices  A & B.

Product 2 uses devices A & B (Same exact devices as Product 1)

Product 3 uses devices X & B

 

If an end user selects Product 1 and Product 2 from a slicer then the Sales need to be:

 

Sales = [(Number of units sold of device A for Product 1 + Number of units sold of device A for Product 2)/2*]

                                                                                            +

               [(Number of units sold of device B for Product 1 + Number of units sold of device B for Product 2)/2*]

 

*The sum of the units sold is divided by 2 because Product 1 & Product 2 uses the same exact devices and there would be duplications of number of devices sold if we don't.

 

If an end user selects Product 1 and Product 3 from a slicer then the Sales need to be:

 

Sales = [(Number of units sold of device A for Product 1 + Number of units sold of device B for Product 1)/1*] +[( Number of units sold of device X for Product 3+ Number of units sold of device Y for Product 3)/1*]

 

*The sum of the units sold is divided by 1 because Product 1 & Product 3 use a different set of devices, therefore there is no duplication in devices used.

 

And if a person selects Products 1, 2, & 3, then the result should be a combination of the 2 equations above.

 

I'm pretty new to Power BI and would really appreciate any help in figuring this out! Thank you in advance! 🙂 

1 ACCEPTED SOLUTION
speedramps
Super User
Super User

I have provided a solution for what you asked for. 

see attached screen print 

Click here to download solution 

 

I want to help but I am an unpaid volunteer, if you cant clearly describe what you want or download the Onedrive solutions then it kind of wastes my time when I could be helping others

 

I have closed the problem. If you need more help them please raise a new ticket and quote @speedramps to send me a notification.

 

Provide input date (as a table not a screen print).

Example of desired output.

A clear decsription.

And one problem per tiket please.  Keep it simple.  

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !

 

 

speedramps_0-1656360235789.png

 

 

View solution in original post

15 REPLIES 15
speedramps
Super User
Super User

I have provided a solution for what you asked for. 

see attached screen print 

Click here to download solution 

 

I want to help but I am an unpaid volunteer, if you cant clearly describe what you want or download the Onedrive solutions then it kind of wastes my time when I could be helping others

 

I have closed the problem. If you need more help them please raise a new ticket and quote @speedramps to send me a notification.

 

Provide input date (as a table not a screen print).

Example of desired output.

A clear decsription.

And one problem per tiket please.  Keep it simple.  

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !

 

 

speedramps_0-1656360235789.png

 

 

speedramps
Super User
Super User

Polite reminder

Remeber we are unpaid volunteers.

Click the thumbs up and accept as solution button. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !

speedramps
Super User
Super User

Polite reminder.

Remeber we are unpaid volunteers.

Click the thumbs up and accept as solution button. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !

Hi,

 

I'm really thankful for what you have done so far, but that does not solve my problem. I did explain what the issue with your solution was. Did you get a chance to look at it?

I thought we had resolved your orginal problem you decsribed,

but you are now changing the description. 

 

It gets a bit complicated to fathom what you want when it is spread over various messages.

Ive tried to understand but you havent explained this very well,

 

Please click this ticket as solved and raise a new fresh ticket.

Provide example input tables (not screen prints).

Example desired output and clear decsription. 

Use the samefield names in the input, output and decsription.

 You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !

Quote @speedramps in the newticket and I will get a notification.

Thanks

Hi speedramps,

 

I have clearly explained the problem in the 3rd message. It doesn't satisfy the 3rd scenario mentioned, therefore it doesn't solve the problem, but if you would like me to accept it as a solution eventhough it is not the solution, I will do it. Let me know. 
Are you still able to find me a solution?

speedramps
Super User
Super User

I tested both links and they work perfectly.

Just  open the onedrive link, click download and it will copy it to your download folder.

You will need Power Bi desktop installed to open the PBIX.

I use Onedrive on the forum all the time and it works ok

The links work fine, but when I click on 'Download', nothing happens. Are you able to send me the solution some other way? 

 

pi.PNG

speedramps
Super User
Super User

Thanks for reaching out for help.

We want to help you, but havent described the problem very well. ☹️

 

Please give exampels of the input data (as tables not screen shots so we can import then amd build solutions).

 

Also give an example the desired output with a detailed and clear explanation. Thanks 😀

 

You say that your have kits which contain components.

 

If customer C1 buys a qty of 2 x P1 (product 1_,  how many A devices do you want reported? 

 

If customer C2 buys a qty of 2 x P1 and 3 x P2,  how many A devices do you want reported?
And why?  I am struugling to undrstand why if you sell 2 kits that you dont sell twice the components

If customer C3 buys a qty of 2 x P1  and
   customer C4 buys a qty of 3 x P2,  how many A devices do you want reported? 

 

If customer C5 buys a qty of 2 x P1  on one day 
and then buys a qty of 3 x P2 on another day,  how many A devices do you want reported? 
Bearing in mind that both items will have been shipped sepearately. 

 

And so on and so on and so on.

 

 

speedramps_0-1655549499306.png

 

 

 

 

                                        

product_family   

 sud

Sales
AVVIGO Guidance System II

OptiCross HD

(OC HD)

10
AVVIGO Guidance System II

Opticross 6 HD

(OC 6 HD)

20
AVVIGO Guidance SystemComet II30
AVVIGO Guidance SystemComet30
Polaris MMG SystemOptiCross HD10
Polaris MMG SystemOpticross 6 HD20

 

Hi speedramps! Thanks a lot for your response and the willingness to help me out. 😀

There are more product families and suds. This is just a sample of the data set i'm using.

 

In this dataset you could see that the product families (AVVIGO Guidance System II & Polaris MMG System) use the same exact suds (OptiCross HD & Opticross 6 HD) and the product family (AVVIGO Guidance System) uses different suds (Comet & Comet II). 

 

I have been asked by the project lead to create a report on Power BI that incluldes a slicer for product_family and a graph that would show sud sales and so on.

 

Scenarios:

 

1) If a user picks AVVIGO Guidance System II & Polaris MMG System from the slicer, then the sud sales would be the addition of the sales units for each sud and dividing it by the number of duplications.

So since AVVIGO Guidance System II & Polaris MMG System use the same exact suds the divisor would be 2.

 

Sales = [(AVVIGO GS II -- OC HD sud sales + Polaris MMGS -- OC HD sud sales)/2]

                                                                        +

               [(AVVIGO GS II -- OC 6 HD suds sales + Polaris MMGS -- OC 6 HD suds sales)/2]

 

2) If a user selects Polaris MMG System & AVVIGO Guidance System from the slicer, then the sud sales would be the addition of the sales unit for each sud and dividing it by the number of duplications.

Since these 2 product families don't share suds then the divisor would be 1.

 

Sales = [(AVVIGO GS -- Comet sud sales + AVVIGO GS -- Comet II sud sales)/1]

                                                                        +

               [(Polaris MMGS -- OC HD suds sales + Polaris MMGS -- OC 6 HD suds sales)/1]

 

3) If a user selects AVVIGO Guidance System II, Polaris MMG System & AVVIGO Guidance System from the slicer then the sales would be,

 

Sales = [(AVVIGO GS II -- OC HD sud sales + Polaris MMGS -- OC HD sud sales)/2]

                                                                        +

             [(AVVIGO GS II -- OC 6 HD suds sales + Polaris MMGS -- OC 6 HD suds sales)/2]

                                                                        +

             [(AVVIGO GS -- Comet sud sales + AVVIGO GS -- Comet II sud sales)/1]

 

I need the graph to show these sales calculations depending on what product families the end user selects. 

Thanks again! I hope this describes the problem better.

Hi again sac_nav

 

Thank you for the example and clear decsription of the problem

Click here to download the solution 

 

 

Your rawdata is not fit for purpose so to you need to clean the data first.

See how in my example dowload.

 

before =  ☹️☹️☹️

speedramps_3-1655831510278.png

 

 

after =  🙂🙂🙂

speedramps_4-1655831525502.png

 

 

The report is then easy .... 

 

 

speedramps_2-1655831390846.png

 

Thanks for reaching out for help.

I have helped you, now please help me by giving kudos.

Remeber we are unpaid volunteers.

Click the thumbs up and accept as solution button. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !

 

🙂

 

Hi speedramps,

 

I can't seem to download the solution. It takes me to a one drive page when I click on the link, but  when I click download, it doesn't do anything.

I think there was a sync problem 
Try this other link or reboot your laptop 

Hi speedramps!

 

So I was able to open the file, but this solution does not solve my problem. 😢

 

For example when I choose

  • AVVIGO Guidance System II and Polaris the sale after the calculation should be [(20+20)/2] + {10+10)/2] = 30
  • Polaris and AVVIGO Guidance System the sales after the calculation should be  [(20+10)/1] +[(30+30)/1] = 90
  • AVVIGO Guidance System II, Polaris and AVVIGO Guidance System the sales after the calculation should be [(20+20)/2] + [(10+10)/2] + [(30+30)/1] = 90

The divisor depends on the number of duplications of suds per selected product. So if there were 3 different products sharing the same suds then the divisor would be three if those 3 products are selected together.

 

I hope gives you a better idea of what I'm trying to do. Thanks in advance!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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