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 / chop it up correctly to get percentage values.

 

Initially my data was 4 tables, but I have merged the Hostname table with the staff table and removed staff rows for which I didn't know the hostname. 

 

 

 

The AppMap table contains the "Shopping App Name" column, which is main title i want to use - this table maps the varying Software Titles in the Installs table to common application titles.

 

I want to create a table, which has the columns : Staff[DisciplineName], AppMap[Shopping App Name], Installation Count and Installation %

 

The Install Data is in the following format :

 

Hostname, SoftwareTitle 

 

I have managed to get a table with the total installs per discipline, but I cannot work out how to do the percentage column?

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,

6 Replies

  • ankitpatira's avatar
    ankitpatira
    Community Champion
    10 years ago

    leinad13 try clicking on dropdown for the Value section -> Quick Calc -> Percentage of grand total. That will display values in percentage. 

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

  • TAlvarez's avatar
    TAlvarez
    Frequent Visitor
    10 years ago

    Is this percentage of the Grand Total or the Column Total that you want?

    • leinad13's avatar
      leinad13
      Advocate I
      10 years ago

      Its the percentage of column total I think i want. I tried grand total, and it was giving me the % installs of all staff and I want the % of install per staff discipline (a column in the Staff table).

      • TAlvarez's avatar
        TAlvarez
        Frequent Visitor
        10 years ago

        A Sum divided by a Calculated Sum filtering the "Row" Dimension should work.

         

        SUM([Value])/CALCULATE(SUM([Value]),ALLSELECTED(RowDimension))

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