AVERAGE on rows in matrix

[QBSRL]

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:

CONS = DIVIDE(AVERAGE('BI TICKETE_HEADER'[SOACTION_DURATA_PVC]),'BI TICKETE_HEADER'[AP])

And I tried this:

CONS = CALCULATE(DIVIDE(

CALCULATE(AVERAGEX('BI TICKETE_HEADER','BI TICKETE_HEADER'[SOACTION_DURATA_PVC]))

,'BI TICKETE_HEADER'[AP]))

But it doesn't work.. I really don't know why display 3.20 or 1.07. I don't understand how it calculates.

Please, any help?

Thank you!

[QBSRL]

@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

[QBSRL]

Thank you a lot! 

The second solution worked.

Thank you again! Have a nice day!

[mahoneypat · Accepted solution]

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