Averaging over multiple rows (all function?)

[Anonymous]

I am trying to get a measure (below). Basically the flag should evaluate to a 1 on both rows because NLR has an average of greater than 0 when looking at the complete table (not just the row current row).

Measurer =
If(CALCULATE(AVERAGEX(f_GW,f_GW[Revenue]),FILTER(f_GW,f_GW[LRFlag]="NLR"))<1,2,1)

LRFlag	Model Name	Revenue	Measure
LR	Generitech	180,000	2
NLR	Generitech	1,250,000	1

[Greg_Deckler]

Perhaps:

If(AVERAGEX(FILTER(ALL(f_GW),f_GW[LRFlag]="NLR"),f_GW[Revenue])<1,2,1)

[Anonymous]

Thanks for the response Greg. Unfortunately this seems to be averaging across all rows in the underlying source table, not just this visualization table. Is there anyway to have average  for just the records displayed on table?

Z

[v-cherch-msft]

Hi @Anonymous

You may refer to below measure. If it is not your case, please explain more about your expect output.

Avg =
CALCULATE (
    AVERAGEX ( f_GW, f_GW[Revenue] ),
    FILTER ( ALL ( f_GW ), f_GW[LRFlag] = "NLR" )
)

Regards,

Cherie

[Anonymous]

The problem is it includes all Model Names in the average.   Notice in the tables below the Average (Measure) is 333,333,33 no matter if I select ABC Company or Generitech? If I select ABC Company it should be 500,000 on both lines and if I select Generitech it should be 250,000 on each line. Basically I need the measure to take into account which model name is selected and it is not. Thanks for the help!

[v-cherch-msft · Accepted solution]

Hi @Anonymous

You may use ALLSELECTED Function  instead of ALL Function.

Measure1 =
CALCULATE (
    AVERAGEX ( f_GW, f_GW[Revenue] ),
    FILTER ( ALLSELECTED ( f_GW ), f_GW[LRFlag] = "NLR" )
)

Regards,

Cherie

[Anonymous]

That did it! Thanks Cherie. - Don't know why but it doesn't have "Accept as a Solution" next to your reply.