Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I would like to calculate average of Amount by some specific column in the same table. Thought this was simple, but I get strange results with this measure, which, I would expect, should do the job:
Calculated Average = CALCULATE(AVERAGE(Table1[Amount]),Table1[DocNo])
This is my source table and the result. Why the measure refuses to calculate average on DocNo, and instead arbitrarily calculates average on Manager?
I am expecting Calculated Average to be like this regardless of Manager field:
Manager DocNo Calculated Average
John 322 35
John 566 20
Peter 123 10
Sam 322 35
-----------------------------
Total: 21.7
21.7 being (35 + 20 + 10) / 3. Standard Quick Measure comes up with 33 in this case.
Solved! Go to Solution.
Hi @gvg
Try this MEASURE
Calculated_Average = IF ( HASONEFILTER ( Table1[DocNo] ), CALCULATE ( AVERAGE ( Table1[Amount] ), ALLEXCEPT ( Table1, Table1[DocNo] ) ), AVERAGEX ( VALUES ( Table1[DocNo] ), CALCULATE ( AVERAGE ( Table1[Amount] ), ALLEXCEPT ( Table1, Table1[DocNo] ) ) ) )
Hi @gvg
Try this MEASURE
Calculated_Average = IF ( HASONEFILTER ( Table1[DocNo] ), CALCULATE ( AVERAGE ( Table1[Amount] ), ALLEXCEPT ( Table1, Table1[DocNo] ) ), AVERAGEX ( VALUES ( Table1[DocNo] ), CALCULATE ( AVERAGE ( Table1[Amount] ), ALLEXCEPT ( Table1, Table1[DocNo] ) ) ) )
Yep, this works like charm. Thank you !
Actually your MEASURE is equivalent to
CalculatedAverage = CALCULATE(AVERAGE(Table1[Amount]),ALL(Table1[DocNo]))
The Calculate Expression
CALCULATE ( <expression>, table[column] = <value> )
is internally transformed into
CALCULATE ( <expression>, FILTER ( ALL ( table[column] ), table[column] = <value> ) )
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |