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.

 

 

 

ProjectPFAvg PFExclude?
Project 12.472.201
Project 21.542.20 
Project 32.592.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

 

 
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

     



     

12 Replies

  • stevedep's avatar
    stevedep
    Memorable Member
    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. 

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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

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

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

    Hope this helps.