Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
101 | |
75 | |
63 | |
62 |
User | Count |
---|---|
143 | |
104 | |
102 | |
81 | |
66 |