Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everybody,
I need the average of certain rows. To illustrate this, a screenshot from Excel:
I need the Close values for which the following applies: weekday = 5. Based on the max date (last available date), I would like to have the last 3 values (outlined in black, always corresponds to index = 0, 1 and 2 per group) so that I can then average them. This should be possible for each group A, B, C .. I think an index column (based on Weekday and Group) has to be created in order to then carry out the calculation with a measure. Anyone have an idea to implement this? Unfortunately, I am already failing in the index column. Maybe you could use topn(...) for each group? Here is the power bi file (with four rows Group, Date, Close, Weekday): https://we.tl/t-MnjAl81PqJ
-> Example Calculation (Group A): 23,45 + 22,6+9,09/3 = ....
Thank you
Best regards, hwoehler
Solved! Go to Solution.
News: I was able to find a solution myself. If someone has an easier one, this is of course also welcome.
Index= If(RS[Weekday]=5;RANKX (FILTER (RS;RS[Group] = EARLIER ( RS[Group] ) && RS[Weekday]=5);RS[Date];;DESC;Dense)-1;Blank())
Average_last_3_weeks = CALCULATE(AVERAGE(RS[Close]);RS[Weekday]=5;Filter(All(RS[Index]);RS[Index]<=2))
Best regards, hwoehler
News: I was able to find a solution myself. If someone has an easier one, this is of course also welcome.
Index= If(RS[Weekday]=5;RANKX (FILTER (RS;RS[Group] = EARLIER ( RS[Group] ) && RS[Weekday]=5);RS[Date];;DESC;Dense)-1;Blank())
Average_last_3_weeks = CALCULATE(AVERAGE(RS[Close]);RS[Weekday]=5;Filter(All(RS[Index]);RS[Index]<=2))
Best regards, hwoehler
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |