Limit of sold product per day

[Anonymous]

Hello,

I have this example of 2 tables, where the first one is number of products sold per day.
The 2nd table is the number of products that we need to sell each day.

Date	Product	Sold
16-10-2019	P1	300
16-10-2019	P1	150
16-10-2019	P2	50
16-10-2019	P2	100
17-10-2019	P2	200
17-10-2019	P3	100

Date	Product	Limit
16-10-2019	P1	500
16-10-2019	P2	100
17-10-2019	P2	300
17-10-2019	P3	100

The image below shows what I want to achieve which is the Limit for each product by day so I can know if the product sales exceeded the limit or no :

 

How can I achieve this with the 'Line and clustered column chart'.

Thank you

[PaulDBrown · Accepted solution]

@Anonymous 

You can get something close to what you are looking for, though not very pretty (you can improve on it by naming the legend appropriately and playing around with the format pane to tweak the bars etc..):

 or you can create a new chart showing the difference between sales and limit:

 Here is the link to the PBI file:

Sales vs Limits PBI file 

Hope that helps

[Anonymous]

• Create the relation between two tables using the Product name
• Use filter date as current date to filter the latest result
• Use line chart for showing the limit and Bra chart for actual sell count
• relation ship will help to achive the same.

[Anonymous]

Thank you but I alread tried that:

• I created relationship between the 2 tables based on concatinate (Product+Date) since if I only put product it will be many to many
•  When I set the limit to the line values : Power BI sums all the values for that day.

Here is the image showing the result : 

[Anonymous]

• Many to many relation ship will not disturb your requirment  it will help you to filter the related data.
• also add the report level filter for Date to filter the report by current date  
• You can change the total sum to distint count :- you will find the option as below

click on graph :- arrow in frant of fields column name:- change to count(Distint)

If you are still unbale to find the option change your column type to NUMBER

[Anonymous]

Thank you for your help, but I am new in PowerBI and I cound't figure it out.
Here is the link to the pbix file if you can help.

https://drive.google.com/open?id=1X4G4EVHDvR6_AA58kOKyk8lbEHqwREGV

[Anonymous · Accepted solution]

Hi @Anonymous Use the Below link, Ihvae created page 2 as per your requirment. 

get Updated PBIX file on below link..

https://drive.google.com/open?id=1ExQnZIh64Epwkfwt9bOubSeKvbTEJjR8

[Anonymous]

@Anonymous @PaulDBrown 
Both solutions are great.
Thank you guys for you time, I really appreciate it.

[PaulDBrown · Accepted solution]

@Anonymous 

You can get something close to what you are looking for, though not very pretty (you can improve on it by naming the legend appropriately and playing around with the format pane to tweak the bars etc..):

 or you can create a new chart showing the difference between sales and limit:

 Here is the link to the PBI file:

Sales vs Limits PBI file 

Hope that helps

[Anonymous]

Hey @Anonymous  i have Updated your PBIX file.

Allow me to Update the PBIX on drive

OR

Share me EmailId so that i can send it to you.

[Anonymous]

Thank you very much.
I shared my email with you in private