PBI Line chart counting numbers below average

[Eggsforbreaky]

Hi all, and thanks in advance. Hoping someone can assist me, I need a measure to count items on a line chart that are below average as below.

 

 

[PhilipTreacy · Accepted solution]

@Eggsforbreaky 

Download PBIX file with the example below

This works for me

Count Below Average = CALCULATE(COUNTROWS('DataTable'), 'DataTable'[Value] < AVERAGE('DataTable'[Value]))

Regards

Phil

[Eggsforbreaky]

Thanks @PhilipTreacy , I am counting events as line items, not an actual value, how would you suggest i do this? 

Throughput =CALCULATE(COUNTA('Item'[ItemId]))

[PhilipTreacy]

@Eggsforbreaky 

Not sure what you mean.  Please provide an example.

COUNTA counts cells in Excel that aren't empty.  If you aren't using actual values, how can you work out an average?

Regards

Phil

[Eggsforbreaky]

I am using date and time slicers to filter the data on the page, each item counted is an event from the sql which i count for throughput. I calculate throughput - 

db Throughput = CALCULATE(COUNTA('Item'[ItemId]))

Hope this helps & thanks again

[PhilipTreacy]

@Eggsforbreaky 

OK so you are counting each 'event' as throughput at a given time.  How do you calculate average over a time period?

In your first image you circled lots of values in red presumably as these were below average.  What was that average?  How did you calculate what the average was?

If you are not using the actual values and just counting any non-blank amount of throughput as an instance - effectively you are counting 1 and 0 - so any time you have no throughput it is below average?

You need to show how you have worked out what is below average in that first image. 

I can't work out an average or what is below that average without adding up some values and dividing by the number of values being counted.

Phil

[Eggsforbreaky]

Hi @PhilipTreacy yes the average and max on the line chart is from the chart analytic's on the visual. I thought maybe if the line chart can work out the average and max there would be an equivent measure to calculate it and find deviations below average? Thanks anyway

[PhilipTreacy]

Hi @Eggsforbreaky 

There is, the measure I wrote.

How is the measure I wrote not what you want?  If you want to count the number of times throughput is below the average throughput for the selected time period, that's what my measure does.

Sorry, I'm not following how I haven't provided the answer you asked for.

Regards

Phil

[Eggsforbreaky]

Sorry @PhilipTreacy  you are right, how would I solve this error? Thanks 

[PhilipTreacy]

@Eggsforbreaky 

You have too many closing square brackets and parentheses.  Try this

db Count Below Average = CALCULATE(COUNTROWS('Item'), 'Item'[ItemId] < AVERAGE('Item'[ItemId]))

Regards

Phil