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

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)





6 Replies

  • nandic's avatar
    nandic
    Resident Rockstar
    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)
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        nandic
        Resident Rockstar
        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