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

[sac_nav]

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! 🙂 

[speedramps · Accepted solution]

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]

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]

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 !

[sac_nav]

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?

[speedramps]

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

[sac_nav]

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]

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

[sac_nav]

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

[speedramps]

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.

[sac_nav]

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 System	Comet II	30
AVVIGO Guidance System	Comet	30
Polaris MMG System	OptiCross HD	10
Polaris MMG System	Opticross 6 HD	20

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.

[speedramps]

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 =  ☹️☹️☹️

after =  🙂🙂🙂

The report is then easy .... 

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 !

🙂

[sac_nav]

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.

[speedramps]

Try this link 

[speedramps]

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

[sac_nav]

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!