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)





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)





  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    It worked !!! 

    Thank you once again, thank you very much!

     

    With the best regards,

    Miguel Freire