cancel
Showing results for
Did you mean:
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.

 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

2 ACCEPTED SOLUTIONS

Accepted Solutions
New Contributor

## Re: Limit of sold product per day

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:

Hope that helps

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

9 REPLIES 9
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.
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 :

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

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.

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.

Regular Visitor

## Re: Limit of sold product per day

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

New Contributor

## Re: Limit of sold product per day

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:

Hope that helps

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

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.

Announcements