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)





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