How to find count of rows greater than average value?

[a4apple]

Hello Everyone,

I have a Measure that calculates the Average of a value in the table.

Now I want to find out the count of records that are greater than the average value.

I want to group the average by Country and date and then find the count of records. How can I write the measure that counts the records in such fashion?

table structure is like below:

Sales : 

CustomerKey  DateKey    Country SalesValue

       1           20160101     US         1.56

       2           20160101     US         2.20

       3           20160101     CA           0

       1           20160102     US         5.20

Average := AVERAGEX(FILTER('Sales', 'Sales'[SalesValue] > 0), 'Sales'[SalesValue])

Count := CALCULATE(COUNTROWS('Sales'), [SalesValue] > [Average]) --> This didn't work gave error

Count := COUNTROWS(FILTER('Sales', 'Sales'[SalesValue] > [Average])) --> This gives the value for all countries. It is not considering US Average for US only Stores / CA average for CA only stores and date as well is not being considered....

Thanks in advance.

[tulasi_pbi1988]

Hello,

I am working on some kind of scenario like this.

If you got a solution, could you give me?

---

@a4apple wrote:

Hello Everyone,

I have a Measure that calculates the Average of a value in the table.

Now I want to find out the count of records that are greater than the average value.

I want to group the average by Country and date and then find the count of records. How can I write the measure that counts the records in such fashion?

table structure is like below:

Sales : 

CustomerKey  DateKey    Country SalesValue

       1           20160101     US         1.56

       2           20160101     US         2.20

       3           20160101     CA           0

       1           20160102     US         5.20

Average := AVERAGEX(FILTER('Sales', 'Sales'[SalesValue] > 0), 'Sales'[SalesValue])

Count := CALCULATE(COUNTROWS('Sales'), [SalesValue] > [Average]) --> This didn't work gave error

Count := COUNTROWS(FILTER('Sales', 'Sales'[SalesValue] > [Average])) --> This gives the value for all countries. It is not considering US Average for US only Stores / CA average for CA only stores and date as well is not being considered....

Thanks in advance.

---

Thank You.

[GilbertQ]

HI @tulasi_pbi1988 

This should work for you

Count = 
VAR MyAverage = CALCULATE(AVERAGE(Table1[SalesValue]),ALL(Table1))
VAR MYSales = SUM(Table1[SalesValue])
RETURN
CALCULATE(COUNTROWS('Table1'), FILTER('Table1',MYSales > MyAverage))

And this is what it looks like

[Sridhar_Cheruvu]

Hi,

Could you get the solution for this problem?

Even I am looking for a similar kind of solution.

Please share if you could resolve your issue.

Thanks,

Sri.

[CheenuSing]

Hi @Sridhar_Cheruvu

Not yet. Will let you know once I find a solution.

Cheers

CheeenuSing

[CheenuSing]

Hi @a4apple

Can you please post more data and the final visual and the output you expect.

Post it in one drive and share.

Cheers

CheenuSing

[a4apple]

FYI, I am trying to do this in SSAS Tabular 1200 Compatibility.

[GilbertQ]

Hi @a4apple

What if you just create a measure which counts the rows?

And then you can use the measure in the table or visual?