Forum Discussion

leinad13's avatar
leinad13
Advocate I
10 years ago
Solved

Help With Grouping By Multiple Columns

I need to establish what percentage of a group of staff have a particular software title installed. I have all the required data and have related it in PowerBI but I just cant work out how to group /...
Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    10 years ago

    leinad13

     

    In this scenario, you can create several calculated columns in the Installs table. Please refer to following steps:

    1. Create a calculated column to store the Discipline name.
      Discipline = 
RELATED ( Staff[DisciplineName] )
    2. Create a calculated column to store the total installs per application per discipline.
      Total_Installs_PerApp_PerDiscipline = 
CALCULATE (
    COUNTROWS ( Installs ),
    FILTER (
        Installs,
        Installs[Discipline] = EARLIER ( Installs[Discipline] )
            && Installs[SoftwareTitle] = EARLIER ( Installs[SoftwareTitle] )
    )
)
    3. Create a calculated column to store the total installs per application in all disciplines.
      Total_Installs_Per_App = 
CALCULATE (
    COUNTROWS ( Installs ),
    ALLEXCEPT ( Installs, Installs[SoftwareTitle] )
)
    4. Create a calculated column to store the percent of install per staff discipline.
      Percentage_PerApp_PerDiscipline = 
Installs[Total_Installs_PerApp_PerDiscipline]
/ Installs[Total_Installs_Per_App]
    5. Drag the Table chart into your canvas as below.

     

    Regards,

v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
10 years ago

leinad13

 

In this scenario, you can create several calculated columns in the Installs table. Please refer to following steps:

  1. Create a calculated column to store the Discipline name.
    Discipline = 
RELATED ( Staff[DisciplineName] )
  2. Create a calculated column to store the total installs per application per discipline.
    Total_Installs_PerApp_PerDiscipline = 
CALCULATE (
    COUNTROWS ( Installs ),
    FILTER (
        Installs,
        Installs[Discipline] = EARLIER ( Installs[Discipline] )
            && Installs[SoftwareTitle] = EARLIER ( Installs[SoftwareTitle] )
    )
)
  3. Create a calculated column to store the total installs per application in all disciplines.
    Total_Installs_Per_App = 
CALCULATE (
    COUNTROWS ( Installs ),
    ALLEXCEPT ( Installs, Installs[SoftwareTitle] )
)
  4. Create a calculated column to store the percent of install per staff discipline.
    Percentage_PerApp_PerDiscipline = 
Installs[Total_Installs_PerApp_PerDiscipline]
/ Installs[Total_Installs_Per_App]
  5. Drag the Table chart into your canvas as below.

 

Regards,

leinad13's avatar
leinad13
Advocate I
10 years ago

v-sihou-msft Many thanks for your detailed an accurate response. It was exactly what i needed.