Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am trying to create a matrix that compares the percentage of labor that each employee used in the selected labor type (chosen from a slicer) during each period over the course of the year. Below I have recreated what this matrix would look like for when three different labor types have been selected. So each employee in each month would have a percentage that totals to 100% across the different labor groups. So Bob in month 1 is 90%+6%+4%=100. The example data won't come out to exactly 100% below because I only have shown three labor types when there are a varying number. Any help with this would be appreciated.
And Below is the layout of my data. I believe the data below matches the exact percentages that I gave in the example above.
Employee | Month | Labor Type | Labor Cost |
Bob | 1 | 1 | 712.8 |
Bob | 1 | 1 | 343.2 |
Bob | 1 | 1 | 132 |
Bob | 1 | 2 | 26.4 |
Bob | 1 | 2 | 52.8 |
Bob | 1 | 3 | 13.2 |
Bob | 1 | 3 | 39.6 |
Bob | 2 | 1 | 290.4 |
Bob | 2 | 1 | 924 |
Bob | 2 | 2 | 26.4 |
Bob | 2 | 2 | 13.2 |
Bob | 2 | 2 | 13.2 |
Bob | 2 | 3 | 13.2 |
Bob | 2 | 3 | 13.2 |
Dave | 1 | 1 | 957.1 |
Dave | 1 | 2 | 90.08 |
Dave | 1 | 2 | 22.52 |
Dave | 1 | 2 | 11.26 |
Dave | 1 | 3 | 11.26 |
Dave | 1 | 3 | 0 |
Dave | 2 | 1 | 821.98 |
Dave | 2 | 1 | 157.64 |
Dave | 2 | 2 | 67.56 |
Dave | 2 | 2 | 33.78 |
Dave | 2 | 3 | 11.26 |
Solved! Go to Solution.
Hi @AM_VRSResearch ,
According to your description, here's my solution.
Create a measure.
Measure =
DIVIDE (
SUM ( 'Table'[Labor Cost] ),
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Employee] = MAX ( 'Table'[Employee] )
&& 'Table'[Month] = MAX ( 'Table'[Month] )
),
'Table'[Labor Cost]
)
)
Put the measure in the matrix Values, get the correct result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @AM_VRSResearch ,
According to your description, here's my solution.
Create a measure.
Measure =
DIVIDE (
SUM ( 'Table'[Labor Cost] ),
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Employee] = MAX ( 'Table'[Employee] )
&& 'Table'[Month] = MAX ( 'Table'[Month] )
),
'Table'[Labor Cost]
)
)
Put the measure in the matrix Values, get the correct result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Share the source data.
Hello, I have added an example of what my data looks like to my post.
Thank you.
Share data in a form that can be pasted in an MS Excel workbook.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |