Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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.
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
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.
Not yet. Will let you know once I find a solution.
Cheers
CheeenuSing
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
FYI, I am trying to do this in SSAS Tabular 1200 Compatibility.
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?
User | Count |
---|---|
104 | |
92 | |
88 | |
78 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |