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.

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

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)