cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ilyes Regular Visitor
Regular Visitor

Limit of sold product per day

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.

 

DateProductSold
16-10-2019P1300
16-10-2019P1150
16-10-2019P250
16-10-2019P2100
17-10-2019P2200
17-10-2019P3100

 

DateProductLimit
16-10-2019P1500
16-10-2019P2100
17-10-2019P2300
17-10-2019P3100

 

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 :

limit.png 

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

Thank you


2 ACCEPTED SOLUTIONS

Accepted Solutions
PaulDBrown New Contributor
New Contributor

Re: Limit of sold product per day

@Ilyes 

 

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..):

 

Sales vs Limits 1.JPG

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

 

Sales vs Limits.JPG

 Here is the link to the PBI file:

Sales vs Limits PBI file 

 

Hope that helps

View solution in original post

prashant74_er Frequent Visitor
Frequent Visitor

Re: Limit of sold product per day

Hi @Ilyes 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

View solution in original post

9 REPLIES 9
prashant74_er Frequent Visitor
Frequent Visitor

Re: Limit of sold product per day

  • 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.
Ilyes Regular Visitor
Regular Visitor

Re: Limit of sold product per day

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 : 


result.png

prashant74_er Frequent Visitor
Frequent Visitor

Re: Limit of sold product per day

  • 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

 

Ilyes Regular Visitor
Regular Visitor

Re: Limit of sold product per day

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

prashant74_er Frequent Visitor
Frequent Visitor

Re: Limit of sold product per day

Hey @Ilyes  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.

 

Ilyes Regular Visitor
Regular Visitor

Re: Limit of sold product per day

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

PaulDBrown New Contributor
New Contributor

Re: Limit of sold product per day

@Ilyes 

 

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..):

 

Sales vs Limits 1.JPG

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

 

Sales vs Limits.JPG

 Here is the link to the PBI file:

Sales vs Limits PBI file 

 

Hope that helps

View solution in original post

prashant74_er Frequent Visitor
Frequent Visitor

Re: Limit of sold product per day

Hi @Ilyes 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

View solution in original post

Ilyes Regular Visitor
Regular Visitor

Re: Limit of sold product per day

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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)