Forum Discussion
AVERAGE on rows in matrix
Hi,
I'm new in Power Bi, and I can't use the AVERAGE function correctly.
So, I have this hierarchy in matrix:
First, please explain me, how is calculate first column, 3.20? I have value SOACTION_DURATA_PVC and I choose "Average".
I expected it to look like this: 4+2+2 = 8, then 8/3 (because I have 3 rows) = 2.66.
So, because display 3.20... I counted the rows separately with distinct count, column AP. 3 it's correct.
Then, column CONS it's a measure, I want 4+2+2 = 8 / 3 = 2.66.
I tried this:
And I tried this:
I don't know the name of your lowest level column (the one with ETA J3, ETA J7, etc.) but you can use that in a measure like this.
NewMeasure =
AVERAGEX (
VALUES ( Table[ColumnWithETA] ),
CALCULATE (
AVERAGE ( Table[Soaction_Durata_PVC] )
)
)Regards,
Pat
4 Replies
- mahoneypat
Microsoft Employee
I don't know the name of your lowest level column (the one with ETA J3, ETA J7, etc.) but you can use that in a measure like this.
NewMeasure =
AVERAGEX (
VALUES ( Table[ColumnWithETA] ),
CALCULATE (
AVERAGE ( Table[Soaction_Durata_PVC] )
)
)Regards,
Pat
mahoneypat It Works! Thank you a looooott! You saved me.
There is the possibility when I collapse level, calculate again?
So, now it's correct here: column AVG
But here, collapse, I want :
column AVG = 1+2.67+14+3+34+1+7.67 = 63.34 / 7 rows = 9.04, not 7.64.Thank you!
- mahoneypat
You can try two approaches for that. Try the first one first as it will be more performant but you may need the second to get your results.
First =
AVERAGEX (
SUMMARIZE (
Table,
Table[BlocColumn],
Table[ETAColumn]
),
CALCULATE (
AVERAGE ( Table[Soaction_Durata_PVC] )
)
)Second =
AVERAGEX (
VALUES ( Table[BlocColumn] ),
AVERAGEX (
VALUES ( Table[ETAColumn] ),
CALCULATE (
AVERAGE ( Table[Soaction_Durata_PVC] )
)
)
)Regards,
Pat