Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Measure not included in average calculation

Hello, I'm trying to get an average from a measure but the last row value (10) is not recognized.   Measure Adj_Avg: If(Calculate(Average('Planned'))=Blank(),10, Average(Billed))     For W...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

    Here I will show you two ways to achieve your goal.

    Way1: You can use [Measure Adj_Avg] as the basic and calculate the average of this measure to get correct result in Total.

    Way1 = AVERAGEX('Date',[Measure Adj_Avg])

    Way2: Try this measur, to get correct result directly.

    Way2 = 
VAR _T1 =
    ADDCOLUMNS (
        'Date',
        "Average of Billed", CALCULATE ( AVERAGE ( Billed[Billed] ), ALLEXCEPT ( Billed, Billed[Date] ) ),
        "Average of Planned", CALCULATE ( AVERAGE ( Planned[Planned] ), ALLEXCEPT ( Planned, Planned[Date] ) )
    )
VAR _T2 =
    ADDCOLUMNS (
        _T1,
        "Adj_Avg", IF ( [Average of Planned] = BLANK (), 10, [Average of Billed] )
    )
RETURN
    AVERAGEX ( _T2, [Adj_Avg] )

    I build a sample like yours.

    Result is as below. 

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

    I build a sample to have a test, but the measure in my sample works well.

    Employee:

    Billed:

    Planned:

    Relationship:

    Measure:

    Measure = IF(AVERAGE(Billed[Heures facturées]) <20,1,0)
    Measure 2 = SUMX('Employee',[Measure])

    Result:

    I think your problem should be caused by your data model, please check your data model. Employee Table should have all employee name in it. Or you can try Way2 to build a summarize table by code and calculate the result in the summarize table.

    If this reply still couldn't help you solve your problem, I suggest you to submit a new post with more details about data model. Our Employeers will help you solve your problem as soon as possible.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Greg_Deckler , thanks for the quick answer. After reading the posts and trying formulas, I couldn't figure it out.

     

    In the example in the the post you suggested, I can't seem to understand how to fit multiple criterias from different tables and why/how to inverse them like you did (see red part highlighted).

    IF(HASONEFILTER(Table[Year]),
    IF(SUM(Table[Amount])<1000,0,SUM(Table[Amount])-1000),
    SUMX(FILTER(Table,[Amount]>1000),[Amount]-1000)
    )

     

    Would you be able to guide me with the formula needed for my problem? Actually, I just showed a simple example but my real formula is a little more complicated. Here it is in 'criteria'. Values come from 2 different tables as you can see.

     

    Var Planned = AVERAGE('Données_SAP'[Billed])
    Var Billed = AVERAGE('Données_Staffing'[planned)
    Var Criterias=
    IF(and(planned=blank(),billed=blank()),blank(),
    if(and(planned=bLANK(),billed>0),10,planned))

     

    Thank you  

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      5 years ago

      Anonymous What is the RETURN statement? I generally first create a measure that works correctly at the individual row level. After that, you create a second measure that has the HASONEVALUE criteria in it. If you have one value (in your case the level below Bureau) then you just return that measure. However, otherwise you would do a SUMX of a SUMMARIZE or SUMMARIZECOLUMNS of your table at the row granularity required (in your case the level below Bureau) with your original measure on the rows of that SUMMARIZE. So, let's say the level below bureau is called "thing". You get your first measure working and then you would do something like:

      Adj_Avg_Total_Measure =
  IF(
    HASONEVALUE(
      'Table'[Thing]),
      [Your Original Measure],  //this is for a single row
      AVERAGEX( //use whatever aggregation is required, SUMX, MAXX, etc.
        SUMMARIZE('Table',[Thing],"__Measure",[Your Original Measure]), //recreates table
        [__Measure] 
      ) 
  )

      So the SUMMARIZE is effectively recreating the matrix/table visualization in memory and you are then aggregating across the rows of that matrix/table in order to get the answer you would expect if this were Excel.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hello Greg_Deckler ,

         

        The return statement is your formula! hehe. But to answer your question, the return statement you were meaning is Criterias. I put it as a variable to make it easier for me to understand your formula.

        Var Planned = AVERAGE('Données_SAP'[Billed])

        Var Billed = AVERAGE('Données_Staffing'[planned)

        Var Criterias=

        IF(and(planned=blank(),billed=blank()),blank(),

        if(and(planned=blank(),billed>0),10,planned))

         

        However, I still unfortunately really don't get how to use your formula.

         

        Here are my tables

         

        I know where to put the return statement, but for the rest…

         

        Adj_Avg_Total_Measure =

          IF(

            HASONEVALUE(

              'Table'[Thing]),

              Criterias,

              AVERAGEX(

                SUMMARIZE('Table',[Thing],"__Measure",Criterias),

                [__Measure]

              )

          )

         

        • Which one of the three tables I should use in the formula?
        • What should I put for _Measure?
        • What should I put for  [__Measure]?

        Thank you very much for your patience!