Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there.
I created a matrix which looks like pictcure below. My question is how can I calculate % from row grand total for complementary worker and regular wokrer as well directly in matrix.
My source data looks like this.
Thanks in advance
Solved! Go to Solution.
Try the following measures:
1- Create grand meausre:
Grand = COUNT('Table'[Employee ID])
2- Create CW%
CW% = var x = CALCULATE(COUNT('Table'[Employee ID]),'Table'[Employee Type]="C") return DIVIDE(x,[Grand],0)
3- Create RW%
RW% = var x = CALCULATE(COUNT('Table'[Employee ID]),'Table'[Employee Type]="R") return DIVIDE(x,[Grand])
Create the below measures:
1- Grand Total
Grant Total = SUM('Table'[CW]) + SUM('Table'[REG])
2- CW %
CW% = DIVIDE(SUM('Table'[CW]),[Grant Total],0)
3- REG%
REG% = DIVIDE(SUM('Table'[REG]),[Grant Total],0)
Thanks for your advice. Problem is that I have CW/Regular employee only in one column with name status (as on second picture in my original post) moreover if I try SUM funtion it won't work with string data type. In other words I need add percentage from total employee by site.
I believe your data table looks more like:
Site|Employee ID| Employee Type|Value
A |123|CW |10
B |312|REG|200
C |414|REG|30
Right?
Nope, my raw data looks like this:
Employee ID| Site| Employee Type
123| Site A| Regular employee
321| Site B| Complementary employee
456| Site C| Regular employee
654| Site C| Complementary employee
etc.
Please mark there is no value column in my data source, it other words every row is a one concrete employee. In excel there was easy way via pivot table to connect site name with count of employe type and then calulate % from it. I am strugeling to get percentage column of complementary and regular workers from total site employees in power bi. Please consider my first picture as matrix pattern not data source.
Try the following measures:
1- Create grand meausre:
Grand = COUNT('Table'[Employee ID])
2- Create CW%
CW% = var x = CALCULATE(COUNT('Table'[Employee ID]),'Table'[Employee Type]="C") return DIVIDE(x,[Grand],0)
3- Create RW%
RW% = var x = CALCULATE(COUNT('Table'[Employee ID]),'Table'[Employee Type]="R") return DIVIDE(x,[Grand])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |