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
Highlighted
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 Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)