Measure not included in average calculation

[Anonymous]

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 Whatever reason, the average of Adj_Avg measure column is stuck at 23,8. It should be 25,83 (it doesn't take into account the 10).

Any ideas?

Thank you

[v-rzhou-msft · Accepted solution]

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.

[v-rzhou-msft · Accepted solution]

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]

Hi @v-rzhou-msft , I understand why it isn't working. My Employee table was made up from a Dax fusion of two tables. I create the table fusion in Power Query and the formula is now working. Thank you

[Anonymous]

Hello @v-rzhou-msft 

I'm confused because I'm having the same issue with another similar example. I tried your technique again and this time it's not working.

Employé from ’Employee’ Table

Heure prévues from ‘Planned’ table

Heures facturées from ’Billed‘ table

I want to count the number of employees that have Heures prévues under 20.

Mesure: IF(Heures facturées <20,1,0). Everything works fine

Nb_Employees measure: SUMX('Employees',[Mesure])

It’s not counting the line where Heures facturées is empty.

Thank you

[v-rzhou-msft · Accepted solution]

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]

Super Simple solution. Thank you very much!

[Greg_Deckler]

@Anonymous This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[Anonymous]

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]

@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]

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!

[Greg_Deckler]

@Anonymous I am thinking the table with the Bureau column in it? Or you might be OK with your Date[Date] column. Depends on how the visual is setup. The [Measure] should be:

[Measure Adj_Avg]. The [__Measure] you leave alone.