cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Zagzebski Member
Member

Averaging over multiple rows (all function?)

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)

 

LRFlagModel NameRevenueMeasure
LRGeneritech              180,0002
NLRGeneritech           1,250,0001

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Averaging over multiple rows (all function?)

Hi @Zagzebski

 

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" )
)

1.png

Regards,

Cherie

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Super User
Super User

Re: Averaging over multiple rows (all function?)

Perhaps:

 

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Zagzebski Member
Member

Re: Averaging over multiple rows (all function?)

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

Community Support Team
Community Support Team

Re: Averaging over multiple rows (all function?)

Hi @Zagzebski

 

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" )
)

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Zagzebski Member
Member

Re: Averaging over multiple rows (all function?)

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!

 

sample2.JPGsample1.JPG

Community Support Team
Community Support Team

Re: Averaging over multiple rows (all function?)

Hi @Zagzebski

 

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" )
)

1.png

Regards,

Cherie

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Zagzebski Member
Member

Re: Averaging over multiple rows (all function?)

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