Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have a matrix with Employee as my Row, Location as my Column, and Count of Task (as a percent of row total).
Employee | Location 1 | Location 2 | Location 3 | Total |
Bob | 25% | 70% | 5% | 100% |
Mary | 50% | 50% | 100% | |
Joe | 100% | 100% |
I would like to create an actual measure that does the same thing as % of row total, without having to have everything on a row.
The data would look like this:
Employee | Location | Task Count |
Bob | Location 1 | 50 |
Bob | Location 2 | 140 |
Bob | Location 3 | 10 |
Mary | Location 1 | 100 |
Mary | Location 2 | 100 |
Joe | Location 1 | 150 |
How can I write this in a measure so that my matrix could be:
Rows:
Employee
Location
Values:
% per location
Bob | 100% |
Location 1 | 25% |
Location 2 | 70% |
Location 3 | 5% |
Mary | 100% |
Location 1 | 50% |
Location 2 | 50% |
Joe | 100% |
Location 1 | 100% |
Thank you
Solved! Go to Solution.
I think I have it-
I created a measure to represent the denominator I'm looking for:
Denominator = CALCULATE(COUNT([Tasks]), REMOVEFILTERS([Location])
Then, I replaced this in the second half of your measure above and got
Hi @aflintdepm
try a measure
Measure =
DIVIDE(
CALCULATE(SUM('Table'[Task Count])),
CALCULATE(SUM('Table'[Task Count]), ALLEXCEPT('Table', 'Table'[Employee]))
)
Thank you for the direction. It's close, but not exactly what I'm expecting. It appears that it is measuring the number of tasks at the location as the denominator.
Here is what I get in my first matrix, set to "show as % of row total"- no measures, just task counts
Here's the exact same employee in the same 2 locations using the measure you described
I'm not sure if the ALLEXCEPT in the second half of the DIVIDE syntax should be the employee, or the location?
Basically, for the DIVIDE function, the numerator would be all tasks performed by the employee in that location and the denominator would be all tasks peformed by the employee in any location
Any suggestions?
I think I have it-
I created a measure to represent the denominator I'm looking for:
Denominator = CALCULATE(COUNT([Tasks]), REMOVEFILTERS([Location])
Then, I replaced this in the second half of your measure above and got
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
91 | |
87 | |
80 | |
69 | |
69 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |