Help With Grouping By Multiple Columns

[leinad13]

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?

[v-sihou-msft · Accepted solution]

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

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

[TAlvarez]

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

[leinad13]

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]

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

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

[ankitpatira]

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