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

     



     

VijayP's avatar
VijayP
Community Champion
5 years ago

hackfifi 

Will this formula helps you!? Please Share your Kudoes

 

hackfifi's avatar
hackfifi
Helper V
5 years ago

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's avatar
    VijayP
    Community Champion
    5 years ago

    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'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