Average of a table with filters

[Anonymous]

HI,

I have three kind of datas, "Planta", "SBH", "Unidad" and "Horas". Each Planta has many SBH, each "SBH" has many "Unidad", each "Unidad" has a related "Horas". Now i create a measure call "Promedio" which is the average of Horas.

When I put in a table SBH and Promedio it gets the average for each "Horas" of the "Unidad" related with the SBH. (Perfect).

Now i have the following problems:

1. I need the average of the above column Promedio, it should give me 88.4%, but in the total is givin me 89.54% and i think is because that total is the average of the hours of every Unidad, and what i need is the average of the values of the  above column Promedio. 

2. In the above table there are a column call Planta, I need an average of the values of the column Promedio (of the above table) for each Planta.

[amitchandak]

Share your average formula.

In case you need Avg of Avg refer

https://community.powerbi.com/t5/Desktop/SUM-of-AVERAGE/td-p/197013

[MFelix]

Hi @Anonymous ,

Measures are calculated based on context. The context can be given by a number of things such as slicers, filters, columns, parameters and so on. When you place an average the calculation is made based on the context so the total value on a table visualization is a level up on the context so you are not getting the average of the Promedio but the average of the hours without any of the columns to give it context.

In this case you need to use the AVERAGEX that is a aggregator formula, and on Planta level:

AVERAGE PROMEDIO = IF(HASONEVALUE(Table[Planta]); Promedio; AVERAGEX(ALLSELECTED(Table[Planta]);[Promedio]))

This may need some adjustments but should work if you don't want to use two measure you can change the [Promedio] above by the calculation you are using.

[Anonymous]

Hi @MFelix 

Thanks for your help, but i can´t use the measure "Average Promedio" you are givin because "Promedio" is a meaure and the expresio ALLSELECTED( ) needs a column as an input.

[MFelix]

Hi @Anonymous ,

The measure is not using promedio as an allselected input the Planta column is the one in the all selected:

AVERAGE PROMEDIO =
IF (
    HASONEVALUE ( Table[Planta] );
    [Promedio];
    AVERAGEX ( ALLSELECTED ( Table[Planta] ); [Promedio] )
)

In my previous post I forgot to put the Promedio in the second if clause with the brackets.

If the column that you need to calculate the average of averages is no Planta replace with the one you need.

[Anonymous]

Thanks @MFelix .

It works for my first problem, but for the second problem its not working, it still gives me the same result as if it is an average with no context as you explained before.

Do you know why?

[MFelix]

Hi @Anonymous ,

Picking up just on the first to line that are from the same Planta is the result correct or do you need it to be the same so:

Line 1 - 99,73

Line 2 - 96,65

Or should you have:

Line 1 - 98,19 ((99,73 + 96,65)/2)

Line 2 - 98,19 ((99,73 + 96,65)/2)

Wich of the results is correct? Or is your problem on a diffenrent level than the line values?

[Anonymous]

@MFelix 

yes i should have 

Line 1 - 98,19 ((99,73 + 96,65)/2)

Line 2 - 98,19 ((99,73 + 96,65)/2)

[MFelix · Accepted solution]

Hi @Anonymous ,

You need to go one level lower in the last part of the measure:

AVERAGE PROMEDIO =
IF (
    HASONEVALUE ( Table[SBH] );
    [Promedio];
    AVERAGEX ( ALLSELECTED ( Table[SBH] ); [Promedio] )
)

Should work as needed.

If the final line gives incorrect values try to replace by this one.

AVERAGE PROMEDIO =
IF (
    HASONEVALUE ( Table[Planta] );
    [Promedio];
    AVERAGEX ( ALLSELECTED ( Table[SBH] ); [Promedio] )
)