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

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:

April 15370
January 14046
March 15656
May 14810

So the mean is 14680


Then if I filter to product X I get

April 286
January 271
March 271


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

Re: Need help creating SPC Charts with filter option



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]))


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

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)