Forum Discussion

hackfifi's avatar
hackfifi
Helper V
5 years ago
Solved

Calculating Average excluding MEASURE data

  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     Cu...
Need Help
  • stevedep's avatar
    stevedep
    5 years ago

    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. 

  • Fowmy's avatar
    Fowmy
    5 years ago

    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's avatar
hackfifi
Helper V
5 years ago

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's avatar
Fowmy
Super User
5 years ago

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's avatar
    hackfifi
    Helper V
    5 years ago

    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's avatar
      Fowmy
      Super User
      5 years ago

      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's avatar
        hackfifi
        Helper V
        5 years ago

        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.