Measure for Percent of Task by Location or Department

[aflintdepm]

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

[az38]

Hi @aflintdepm 

try a measure

Measure = 
DIVIDE(
    CALCULATE(SUM('Table'[Task Count])),
    CALCULATE(SUM('Table'[Task Count]), ALLEXCEPT('Table', 'Table'[Employee]))
)

[aflintdepm]

@az38 

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?

[aflintdepm · Accepted 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

Percent per Office = DIVIDE(

    CALCULATE(SUM([TASK COUNT])),

    [DENOMINATOR]

)

This matched my original matrix exactly, but allowed me to group the locations per my desired format.  Thank you for you help and getting me moving in the right direction.