Calculating Average excluding MEASURE data

[hackfifi]

Hello - Trying to develop a measure excluding certain data.

Project	PF	Avg PF	Exclude?
Project 1	2.47	2.20	1
Project 2	1.54	2.20
Project 3	2.59	2.20

Currently Average is calculated correctly [2.47 + 1.54 + 2.59] / 3 = 2.20

But i wanted to ignore Project 1 in the average calculation (as exclude = 1). Hence i want the average PF to be [1.54 +2.59] = 2.07

I am currently using the below formula, but it is still considering all 3 projects

Avg PF =

var excl = [Exclude?]

return

CALCULATE(AVERAGEX(Projects_Alias,[PF]),filter('Ref Project',excl<>1),ALLSELECTED(Projects_Alias[Project]))

I am trying to show a CHART (Column-Line) visual, and hence i need to show all the 3 x Projects PF values in columns, but the average line should be based on 2 x Projects.

Kindly note "Exclude" is a measure based on=

if(MAX(Projects_Alias[Project]) in ALLSELECTED('Ref Project'[Project]),1,blank())

Appreciate the support

[stevedep · Accepted solution]

Hi,

Like this?

___AvgforNonExcluded = CALCULATE(
                            AVERAGE('Table'[PF]), FILTER(ALL('Table'),'Table'[Exclude]=BLANK()))

As seen here:

Link to file. 

Please mark as solution if so. Thumbs up for the effort are appreciated.

Kind regards, 
Steve. 

[stevedep · Accepted solution]

Hi,

Like this?

___AvgforNonExcluded = CALCULATE(
                            AVERAGE('Table'[PF]), FILTER(ALL('Table'),'Table'[Exclude]=BLANK()))

As seen here:

Link to file. 

Please mark as solution if so. Thumbs up for the effort are appreciated.

Kind regards, 
Steve. 

[Ashish_Mathur]

Hi,

Does this measure work?  Drag this measure to a card visual.

=averagex(filter(values(data[project]),data[exclude?]<>1),[pf])

Hope this helps. 

[VijayP]

@hackfifi 

Will this formula helps you!? Please Share your Kudoes

[hackfifi]

@VijayP  - can you copy / paste the code please?

Also FYI -

"PF" is a measure

i have a filter for selection of projects i.e. Projects_Alias

and also a filter for selection of refrence projects i.e. 'Ref Project'. The list of projects is the same of both filters

But when a project is selected from 'Ref Project', it calculates a "1" for the "exclude measure" 

[VijayP]

@hackfifi 
create PF as a column and use this measure to get the result

IF you want use PF As measure Change 'Table'[PF] to [PF] and remaining formula will be same

AVG PF2 =
VAR totalpf = CALCULATE(SUM('Table'[PF]),All('Table'))
VAR prjct1pf = CALCULATE(SUM('Table'[PF]),
FILTER(ALL('Table'),'Table'[Project]=SELECTEDVALUE('Table'[Project])))

VAR balance = totalpf-prjct1pf
VAR cnt = CALCULATE(COUNT('Table'[Project]),All('Table'))
VAR selectcnt = CALCULATE(COUNT('Table'[Proejct]),
FILTER(ALL('Table'),'Table'[Project]=SELECTEDVALUE('Table'[Project])))
VAR cntbalance = cnt-selectcnt
RETURN
Divide(balance,cntbalance,0)

[hackfifi]

Sorry @VijayP  it did not work...i was getting AVGPF2 value is 0

i have to use "ALLSELECTED" as i have over 100 projects in the dataset, but i have selected only 3

I am essentially trying to calculate the average PF of SELECTED PROJECTS where EXCLUDE<>1

Avg PF2 =

var totalpf=CALCULATE([PF],ALLSELECTED(Projects_Alias[Project]))

var project1pf = CALCULATE([PF],filter(ALLSELECTED(Projects_Alias),Projects_Alias[Project]=SELECTEDVALUE(Projects_Alias[Project])))

var balance = totalpf-project1pf

var cnt = CALCULATE(COUNT(Projects_Alias[Project]),All(Projects_Alias))

VAR selectcnt = CALCULATE(COUNT(Projects_Alias[Project]),

FILTER(ALLSELECTED(Projects_Alias),Projects_Alias[Project]=SELECTEDVALUE(Projects_Alias[Project])))

VAR cntbalance = cnt-selectcnt

RETURN

Divide(balance,cntbalance,0)

[Fowmy]

@hackfifi 

You may try this measure:

Avg PF (Excluded) = 
VAR __Exclude = [Exclude] RETURN
AVERAGEX(
    FILTER( ALLSELECTED(Table2[Project], Table2[PF]) ,  __Exclude <> 1),
    Table2[PF]
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[hackfifi]

hi @Fowmy ...kindly note PF is a measure, so i modified your formula as below

Avg PF (Excluded) =

VAR _Exclude = [Exclude?]

RETURN AVERAGEX( FILTER( ALLSELECTED(Projects_Alias[Project]), _Exclude <> 1), [PF] )

But i am getting the below result:

[Fowmy]

@hackfifi 

Then use it this way, if it doesn't work,  show the expected results based on your sample.

Avg PF (Excluded) = 
VAR __Exclude = [Exclude] RETURN
AVERAGEX(
    FILTER( ALLSELECTED(Table2),  __Exclude <> 1),
    [PF]
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[hackfifi]

@Fowmy  - Based on the sample, the expected result should be the average of the exclude <> 1 i.e. (1.54 + 2.59) / 2  = 2.07

Currently the result of your formula is still the average of the 3 data points i.e. (2.47+1.54+2.59)/3 which is 2.20

Hope that is clear, and thanks again for your time.

[Fowmy · Accepted solution]

@hackfifi 

I forgot to remove the variable, as it always stays static, please check now:

Avg PF (Excluded) = 
AVERAGEX(
    FILTER(ALL(Table2[Project]),[Exclude] <> 1),
    [PF]
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[hackfifi]

Thanks @Fowmy - that worked.

So just for my understanding, how did it change by just removing the variable? i thought the variable was doing the same thing?