Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
leinad13
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
v-sihou-msft
Employee
Employee

@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
v-sihou-msft
Employee
Employee

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

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

TAlvarez
Frequent Visitor

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

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).

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

 

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

ankitpatira
Community Champion
Community Champion

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

 

Capture.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.