How to add an "average if" column to query

[AldoMF]

I have this kind of data:

Name	Position	Margin
Dani	Proc	9.2%
Thomas	Sales	62.4%
Kate	Sales	57.7%
Kevin	Proc	7.8%
Judy	Sales	29.2%
Julius	Sales	62.9%
Brett	Proc	92.3%
Tom	OP	17.1%
Gisele	Sales	69.6%
Katie	Sales	76.4%
Hilary	Proc	36.1%
Don	OP	91.1%
Lucy	Sales	69.3%
Dani	Proc	64.1%
Thomas	Sales	27.5%
Kate	Sales	79.8%
Kevin	Proc	63.3%
Judy	Sales	17.9%
Julius	Sales	13.1%
Brett	Proc	42.1%
Tom	OP	90.7%
Gisele	Sales	46.6%
Katie	Sales	86.6%
Hilary	Proc	28.9%
Don	OP	7.6%
Lucy	Sales	39.1%

I'd like to add a new colum that shows the average Margin depending on the person's Position. After that I'd like to make a visualization that shows the avergage margin by name in columns and as constant horizontal lines the average margin by position. Is it possible to do this? Thank you very much for your support

[Vvelarde · Accepted solution]

hola @AldoMF

Replace All with AllSelected

[Vvelarde]

hi @AldoMF

is possible to do.

first: Create a measure to obtain the average margin:

Avg-margin = AVERAGE(Persons[Margin])

next create a measure for each Position

AVG-Margin-OP =
CALCULATE ( [Avg-margin], FILTER ( ALL ( Persons ), Persons[Position] = "OP" ) )

AVG-Margin-Sales =
CALCULATE (
    [Avg-margin],
    FILTER ( ALL ( Persons ), Persons[Position] = "Sales" )
)

AVG-Margin-Proc =
CALCULATE (
    [Avg-margin],
    FILTER ( ALL ( Persons ), Persons[Position] = "Proc" )
)

Finally in a line clustered column chart visual put the columns and measure in this way:

[AldoMF]

Hi @Vvelarde Thank you very much for your answer it worked perfect, however I made a mistake by not including all of the information. My data also contains Months, Years, Quarters, Category and Country, something like this:

Name	Position	Margin	Month	Year	Quarter	Group	Group	Category	Country
Dani	Proc	9.2%	Oct-15	15	4		One	A	Europe
Thomas	Sales	62.4%	Nov-15	15	4		two	B	North America
Kate	Sales	57.7%	Dec-15	15	4		One	D	Asia
Kevin	Proc	7.8%	Jan-16	16	1		two	T	South America
Judy	Sales	29.2%	Feb-16	16	1		One	H	Oceania
Julius	Sales	62.9%	Mar-16	16	1		two	A	Africa

When I used your solution, it showed me the total Margin average, but once I started playing with the visualizations and filtering the visualizations by country, year, quarter, etc. the lines didn't change their value. Is there a way in which I can have those same lines but they change their value as I filter the visualization? My visualization looks something like this:

 Is there a way to make the values of the lines change as I filter the report?

Once again thank you for your help and support! You're awesome!

Hello from Guadalajara, Mexico 🙂

[Vvelarde · Accepted solution]

hola @AldoMF

Replace All with AllSelected

[AldoMF]

Hi @Vvelarde Thank you very much!

Eres un crack

[AldoMF]

Hi @Vvelarde What if I just want to show in the Colums the people with Position "PC" but I need the three lines to still show in the graphs?

[Vvelarde]

@AldoMF

In visual level filter of the chart drag Position and Select the position.

[AldoMF]

@Vvelarde I've done so and the other two lines dissapear because of the "ALLSelected" filter

[Vvelarde · Accepted solution]

@AldoMF

Maybe exist another solution  more easy but try with:

AVG-Margin-Proc = CALCULATE([Avg-margin],FILTER(ALLEXCEPT(Persons,Persons[Year],Persons[Quarter],Persons[Category],Persons[Country],Persons[Group]),Persons[Position]="Proc"))

modified the other measures like this with AllExcept

And Position in a slicer..Don't put in visual level filter.

[AldoMF]

Thanks @Vvelarde! It worked perfectly!