Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
In this scenario, you can create several calculated columns in the Installs table. Please refer to following steps:
Discipline = RELATED ( Staff[DisciplineName] )
Total_Installs_PerApp_PerDiscipline = CALCULATE ( COUNTROWS ( Installs ), FILTER ( Installs, Installs[Discipline] = EARLIER ( Installs[Discipline] ) && Installs[SoftwareTitle] = EARLIER ( Installs[SoftwareTitle] ) ) )
Total_Installs_Per_App = CALCULATE ( COUNTROWS ( Installs ), ALLEXCEPT ( Installs, Installs[SoftwareTitle] ) )
Percentage_PerApp_PerDiscipline = Installs[Total_Installs_PerApp_PerDiscipline] / Installs[Total_Installs_Per_App]
Regards,
In this scenario, you can create several calculated columns in the Installs table. Please refer to following steps:
Discipline = RELATED ( Staff[DisciplineName] )
Total_Installs_PerApp_PerDiscipline = CALCULATE ( COUNTROWS ( Installs ), FILTER ( Installs, Installs[Discipline] = EARLIER ( Installs[Discipline] ) && Installs[SoftwareTitle] = EARLIER ( Installs[SoftwareTitle] ) ) )
Total_Installs_Per_App = CALCULATE ( COUNTROWS ( Installs ), ALLEXCEPT ( Installs, Installs[SoftwareTitle] ) )
Percentage_PerApp_PerDiscipline = Installs[Total_Installs_PerApp_PerDiscipline] / Installs[Total_Installs_Per_App]
Regards,
@v-sihou-msft Many thanks for your detailed an accurate response. It was exactly what i needed.
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))
@leinad13 try clicking on dropdown for the Value section -> Quick Calc -> Percentage of grand total. That will display values in percentage.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |