create measure across multiple manager and over time

[boyddt_mn]

I have created a measure that works for a single manager. This measure takes the number of absences called in by their direct reports and divides it by the number of direct reports. If I toss it in a table it will display a single manager fine, but as soon as I add another manager it crashes.

Ultimately, what I would like to get to, is to have a graph that shows, over time, how this rate is changing for the manager something like the link below.

https://community.powerbi.com/t5/Community-Blog/Adding-Sparklines-to-the-Table-and-Matrix-Visual-No-...

I have included a linkt to an excel spreadsheet - PowerBI_Community_Absence.xlsx - that has the table structures, measure calculation and some other info. I cannot include the existing pbix file as it has personal data in it but if necessary, I can try pulling something together using dummy data.

https://www.dropbox.com/sh/d6gfg9kns6jdtpa/AAANNd305bBeWR3pHJCh-TEla?dl=0 

[v-jingzhang]

Hi @boyddt_mn 

Your original measure crashed because Distinct() function will return a table or a column of distinct values while the '=' expects a single value to be compared. When you add multiple managers, they cannot be compared as Distinct() will return more than one values.

If you want to use the method in the blog you shared, you could try below codes in Power Query. I made a reference query of tblAbsenceCalledIn and applied some transformation steps to get the sparklines column.

let
    Source = tblAbsenceCalledIn,
    #"Grouped Rows" = Table.Group(Source, {"ManagerID", "DateCalled"}, {{"Absence Count", each Table.RowCount(_), Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"ManagerID"}, hrfile, {"Manager ID"}, "hrfile", JoinKind.LeftOuter),
    #"Aggregated hrfile" = Table.AggregateTableColumn(#"Merged Queries", "hrfile", {{"Employee ID", List.Count, "Count of hrfile.Employee ID"}}),
    #"Renamed Columns" = Table.RenameColumns(#"Aggregated hrfile",{{"Count of hrfile.Employee ID", "Employee Count"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Rate", each [Absence Count]/[Employee Count]),
    Custom1 = Table.Group(#"Added Custom", {"ManagerID"}, {{"Sparklines", each sparklinesSVG([DateCalled], [Rate]), type text}})
in
    Custom1

Otherwise, you could also try a custom visual Small Multiple Line Chart and create a measure like below as a value field in the visual.

Rate 2 = DIVIDE(COUNT(tblAbsenceCalledIn[EmployeeID]),COUNT(hrfile[Employee ID]))

Additionally, I created a relationship between hrfile and tblAbsenceCalledIn (one-to-many) on Employee ID. And I assumed that you want to see the rate over Dates. For more details, please refer to this PBIX file. 

Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[boyddt_mn]

Thank you, I will give this a try today.

[v-jingzhang]

Hi @boyddt_mn, does my solution help solve this problem?

[boyddt_mn]

I have been pulled into another 'critical' project and this has been put on hold. I will try you solution sometime this week.