cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leinad13 Frequent Visitor
Frequent Visitor

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. 

 

 

Capture.PNG

 

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-sihou-msft
Moderator

Re: Help With Grouping By Multiple Columns

@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.
    666.jpg

 

Regards,

View solution in original post

6 REPLIES 6
ankitpatira Super Contributor
Super Contributor

Re: Help With Grouping By Multiple Columns

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

 

Capture.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

TAlvarez Frequent Visitor
Frequent Visitor

Re: Help With Grouping By Multiple Columns

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

leinad13 Frequent Visitor
Frequent Visitor

Re: Help With Grouping By Multiple Columns

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 Frequent Visitor
Frequent Visitor

Re: Help With Grouping By Multiple Columns

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

 

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

Moderator v-sihou-msft
Moderator

Re: Help With Grouping By Multiple Columns

@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.
    666.jpg

 

Regards,

View solution in original post

leinad13 Frequent Visitor
Frequent Visitor

Re: Help With Grouping By Multiple Columns

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,189)