Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have a data set like this :
Name Var1
A 5
A 7
B 5
B 2
C 1
C 3
I have to calculate the average of the max of each name. But I must have just the mean value like the table below :
Solved! Go to Solution.
@Jeremy19 , Try like
calculate(averageX(values(Table[Name]), calculate(max(Table[Var1]))),all(Table))
@Jeremy19 I have done it like this
maxval := CALCULATE(MAX('Table'[Column2]),ALLEXCEPT('Table','Table'[Column1]))
maxvalfilter := IF(MAX('Table'[Column2])=[maxval],[maxval])
summaxval := CALCULATE(SUMX('Table',[maxvalfilter]),ALL('Table'))
count := CALCULATE(CALCULATE(COUNTROWS('Table'), FILTER('Table','Table'[Column2]=[maxvalfilter])),ALL('Table'))
average := DIVIDE([summaxval],[count])
Thanks for your help. But the solution does not take into account all the filters of the page like the group or the date.
In my actual dataset the expected value here is 1174
@Jeremy19 I wish you included that in your original question
@Jeremy19 , Please find the attached file after the signature.
Measure = calculate(averageX(values('Table'[Name]), calculate(max('Table'[Var1]))),all('Table'))
It works well on this example but when I add a filter by selecting two names it keeps the value 5
I know sorry I thought that would not change the solution and I wanted to try to simplify the problem
@Jeremy19 , Try measures like
averageX(values(Table[Name]), calculate(max(Table[Var1])))
or
averageX(values(Table[Name]), calculate(max(Table[Var1]), allexcept(Table, Table[Name])))
Thanks @amitchandak . These are solutions that I have already tested but I still get:
@Jeremy19 , Try like
calculate(averageX(values(Table[Name]), calculate(max(Table[Var1]))),all(Table))
This time I have an identical value, but it is not the right result. This measure does not take into account different filters that I have such as the date or the positions of the players.
Just with a small change to adapt to the filters it was the right solution
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |