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

Need help creating an SPC chart with filter capability?

Trying to impress the boss with a fancy SPC chart for monthly sales that can be filtered per product.

 

It's a line chart showing count of invoices (Or sum of revenue) per month. So first I need to add a line showing the average  for all the months (A plain horizontal line, but it needs to adjust depending which product I filter on.)

 

Next is a calculated line (LCL=Mean-3*stddev)

 

Every time I have tried it just sees the invoice numbers as individual numbers and gives an average of that, rather than an average of the count per month.

 

I can easily put in fixed lines for all data, but I specifically want something that will adjust depending on the filter applied. If I can add a banner card that shows the actual calculated number per filter it will also help a lot.

 

Then I also need a way to sort products by a count of all invoice numbers in a bar chart. Is that possible?

 

Thanks in advance.

3 REPLIES 3
JanOos Frequent Visitor
Frequent Visitor

Need help creating SPC Charts with filter option

Hi All

 

I am trying to impress the boss with some SPC Charts that can show either a count of all invoices per month or a sum of revenue per month with some 6sigma based analysis lines on the chart.

 

For this I need a basic line chart that shows the count of invoices accross a few months with a filter per product. Easy enough.

 

Next I get lost: I need to show a mean of the count of invoices accross all the months. It's easy enough to enter a fixed number, but then it doesn't change as I filter per product.

 

For all I have:

February13519
April 15370
January 14046
March 15656
May 14810

So the mean is 14680

 

Then if I filter to product X I get

February291
April 286
January 271
March 271
May 

260

With mean 276.

 

So how do I get my graphs to show a horizontal line of the mean that changes as I filter by product?

 

The next line is the same concept, but the formula is LCL=Mean-(3*StandardDev)

 

Does anyone know how to create these lines on a line graph?

 

 

I also wan't to show all the products with associated revenue on a bar graph, then sort it by amount of revenue so it shows the best performers first.

Moderator Eric_Zhang
Moderator

Re: Need help creating SPC Charts with filter option

@JanOos

 

There's no dynamic reference line feature, you can vote for this idea. So far you can apply a workaround with a measure. To get the second LCL formula, you can tweak the measure accordingly.

avg ref line = CALCULATE(AVERAGE(sales[sales]),ALL(sales[date]))

Capture.PNG

JanOos Frequent Visitor
Frequent Visitor

Re: Need help creating SPC Charts with filter option

Thank you for the response. I tried your formula but unfortunately it returns a 0 value.

 

I have voted for the feature.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,294)