Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

[Grouping Data]

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 l...
Need Help
  • nandic's avatar
    nandic
    6 years ago

    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's avatar
    nandic
    6 years ago

    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)





Fowmy's avatar
Fowmy
Super User
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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