cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SamOvermars
Frequent Visitor

Counting the total produced items over a period of time

Hello,

I have this table structure

ItemItem TypeProduction Date
1Produce8/10/2021
2Fruit8/12/2021
3Produce8/13/2021

 

I added a line chart, added the count of items to the value field(I believe it should be the sum of items within an item type)
and the production date to the axis, but I'm getting the line going up and down which is not what I'm desiring.
it is counting how many items per day, while I want how many items were produced to date. In my example, 8/13 should show 2 "produce" items were produced while only 1 item in 8/10
eventually, I need to have a line chart that will show how many total items were produced over time. something like this

Untitled.png

 

How can I achieve this? 

7 REPLIES 7
PhilipTreacy
Super User
Super User

Hi @SamOvermars 

Download new sample PBIX file.

I'm not clear what the problem is.

In your supplied file it looks like you've tried to add a filter for the Item Type?  If you just want to show certain Items you can add a Slicer and filter those as you wish - see linked file above.

 

You said:

I need to have a filter on the chart that will be based on another value from the table - what other value?

The line is not letting me filter it and will keep calculating just the totals - what do you want it to do?

 

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


PhilipTreacy
Super User
Super User

Hi @SamOvermars 

Can you upload that file somewhere else please?  That site is full of malware and ads and my security software is blocking it.

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


PhilipTreacy
Super User
Super User

Hi @SamOvermars 

Download this PBIX file with the following code

Use a measure like this

CountMeasure = CALCULATE(COUNTROWS('DataTable'), FILTER(ALL('DataTable'), [Item Type] = SELECTEDVALUE('DataTable'[Item Type]) && [Production Date] <= SELECTEDVALUE('DataTable'[Production Date])) )

Then plot that on the Line Chart with the Item Type in the Legend

prodchart.png

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Thank you for the solution it worked to get the totals but I need to have a filter on the chart that will be based on another value from the table. The line is not letting me filter it and will keep calculating just the totals.

For example if I add another column to the filters pane it will not change anything. I re attached the pbix to show what I mean. Thanks again.

pbix 

PhilipTreacy
Super User
Super User

Hi @SamOvermars 

Sounds like you want to create a running total for each Item Type?  And you will have multiple lines, one for each Item or do you want an overall total for all item types meaning the chart will just have 1 line?

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Correct will need to have running total for each item type. in my example the chart will have two lines 

one for "Produce" and one for "Fruit"

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.