[Grouping Data]

[Anonymous]

Hello guys,

So, I am new into PowerBI, and I am using it to develop my master thesis. I have a database with different teams from all of the cities, some of them from the same city, and I would like to create a graphic that could compare the results from one team, with the average results from all the teams in the same city of that one, and with the average results from the overall teams. But I would like it to be dynamic, which means, if I select another team all the graphics readjust.

I don't know if I could express myself very well, or if it's possible to do this. Hope you understand it and can help me.

Thanks in advance, 

Miguel Freire

[nandic · Accepted solution]

@Anonymous ,
Below are formulas:

1) Average for selected team

Avg Selected Team = AVERAGE(Sheet1[Amount])

2) Average for all teams in the same city as seleted team, but without selected team

Avg City =

var _SelectedTeam = SELECTEDVALUE(Sheet1[Team])

var _City = LOOKUPVALUE(Sheet1[City],Sheet1[Team],_SelectedTeam)

var _CityTeams = FILTER(ALL(Sheet1),Sheet1[City]=_City && Sheet1[Team] <> _SelectedTeam)

RETURN

CALCULATE(AVERAGE(Sheet1[Amount]),_CityTeams)

3) Average for all teams in table, but without selected team

Avg All =

var _SelectedTeam = SELECTEDVALUE(Sheet1[Team])

var _All = FILTER(ALL(Sheet1),Sheet1[Team] <> _SelectedTeam)

RETURN

CALCULATE(AVERAGE(Sheet1[Amount]),_All)

[nandic · Accepted solution]

@Anonymous ,
Below are formulas:

1) Average for selected team

Avg Selected Team = AVERAGE(Sheet1[Amount])

2) Average for all teams in the same city as seleted team, but without selected team

Avg City =

var _SelectedTeam = SELECTEDVALUE(Sheet1[Team])

var _City = LOOKUPVALUE(Sheet1[City],Sheet1[Team],_SelectedTeam)

var _CityTeams = FILTER(ALL(Sheet1),Sheet1[City]=_City && Sheet1[Team] <> _SelectedTeam)

RETURN

CALCULATE(AVERAGE(Sheet1[Amount]),_CityTeams)

3) Average for all teams in table, but without selected team

Avg All =

var _SelectedTeam = SELECTEDVALUE(Sheet1[Team])

var _All = FILTER(ALL(Sheet1),Sheet1[Team] <> _SelectedTeam)

RETURN

CALCULATE(AVERAGE(Sheet1[Amount]),_All)

[Anonymous]

Thank you so so much @nandic.

Everything is working perfectly, there is only one more problem I hope you can help me with. That is it:

In my datasheet, the amount of work done can be performed in 4 different types of units: acre, meters, days and number. And this is making the average of everything together without looking for the type of unit. Even if I make a filter on the page for units it only takes influence in the first bar, the other ones don't change.  I only want to select a unit at a time to see in the chart, so if I select "meters" want all the bars to show the average of meters and ignore the others (acre, days and number).

Hope you can understand this, and if you could help me I would be eternally grateful.

Thank you very much once again.

Regards,

Miguel Freire

[nandic · Accepted solution]

@Anonymous glad it worked!
Just add slicer with unit column and make it single select so that user can't select multiple unit type. 
In measures add new variable and filter by selected unit.

Avg All =

var _SelectedTeam = SELECTEDVALUE(Sheet1[Team])

var _SelectedUnit = SELECTEDVALUE(Sheet1[Unit])

var _All = FILTER(ALL(Sheet1),Sheet1[Team] <> _SelectedTeam && Sheet1[Unit]=_SelectedUnit)

RETURN

CALCULATE(AVERAGE(Sheet1[Amount]),_All)

Avg City =

var _SelectedTeam = SELECTEDVALUE(Sheet1[Team])

var _SelectedUnit = SELECTEDVALUE(Sheet1[Unit])

var _City = LOOKUPVALUE(Sheet1[City],Sheet1[Team],_SelectedTeam)

var _CityTeams = FILTER(ALL(Sheet1),Sheet1[City]=_City && Sheet1[Team] <> _SelectedTeam && Sheet1[Unit]=_SelectedUnit)

RETURN

CALCULATE(AVERAGE(Sheet1[Amount]),_CityTeams)

[Anonymous]

It worked !!! 

Thank you once again, thank you very much!

With the best regards,

Miguel Freire

[Fowmy]

@Anonymous 

Can you share some sample data with the location points and the expected result as data to have a clear understanding of your question?

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Anonymous]

I don't know why, but I can't upload images. I will try to explain myself better this time.

I have 3 different columns of data. The first one with the name/code of the teams. The second one with the city/district from where the team belong. And the last one with the amount worked by the team.

Imagine the follow example:

Team        City         Amount

A              Porto       5

B              Porto       10

C              Porto       3

D              Lisboa     5

With this, I want to build a bar chart, with 3 bars. Imagine I select team A. The first bar with the average amount of work performed by the team I select (5 in this case). The second one with the average amount of work performed by all the teams from the same city as the team I select, with the exception of itself ((10+3)/2=6,5). And the last one with the average amount of work performed by all the teams on the datasheet, with the exception of itself ((10+3+5)/3=6).

I think I could easily do this for one example, working with the raw data to perform the graph I want. But I want it to be dynamic, which means that the graph has to readjust all the 3 bars once I select a different team, and this I don't know how to do.

I hope I was more self-explanatory this time. Thanks for trying to help.

Regards,

Miguel Freire