Calculate a Metric for Date Range + Latest Week in Same Matrix

[Anonymous]

I have a report I have been working on to calculate overtime for departments/supervisors/employees. I can create two separate matrix visuals with the latest week OT% (using TopN visual level filter) and one that has all the weeks and the OT%. The problem is I need this in a single matrix so that the user can see the OT% for the latest week selected vs. the OT% for all the weeks in the selected date range - this would help them determine if this week is an isolated incident or a common OT% for that area (e.g. higher than the average OT% for the weeks selected).

No matter what I try (ALL, ALLEXCEPT, ALLSELECTED, VALUES, etc.) I cannot combine these two measures into a single matrix! If anyone can provide some help I would be forever grateful (well maybe not forever, but at least for a little while...).

The PBIX and a sample XLSX source are located here:

https://drive.google.com/drive/folders/15VBBeneWtf7KGDhmdoXPTy12-WEiQQyH

[Anonymous · Accepted solution]

Let's see if this gets us in the right direction.

First thing I did was make separate measures to use in more complex code.  Call these "Base Measures", which is how I was taught:

Total OverTime = 
	SUM ( DataForPBIX[OT Amt])

Total NonOvertime = 
	SUM ( DataForPBIX[NonOT Amt])

Total OT + NonOT = 
[Total Overtime] + [Total NonOvertime]

OverTime % = 
DIVIDE( [Total OverTime],[Total OT + NonOT])

With those in place, had to figure out a way to filter the date in your table.  Decided to go with a parameter table.  

Disconnected Week Table = VALUES('DataForPBIX'[week])

The resulting table:

This will be used to create virtual relationships.  Using that table, we add a filter to the canvas:

Then we create a measure to grab the min and max of those values:

Min of Week Selected = min ( 'Disconnected Week Table'[Week])

Max of Week Selected = MAX( 'Disconnected Week Table'[Week])

RANGE SELECTED = [Min of Week Selected]& " to " &  [Max of Week Selected]

Create a measure to give the Overtime % of the max of the selected:

Latest Week Selected % = 
CALCULATE(
	[OverTime %],
	FILTER(
		 ALL(DataForPBIX[Week]),
		 'DataForPBIX'[Week] = [Max of Week Selected] 
    )
) 

Then create a measure that uses the entire range of the user selected:

Range Selected % = 
CALCULATE(
	[OverTime %],
	FILTER(
		 ALL(DataForPBIX[Week]),
		 DataForPBIX[Week] <= [Max of Week Selected]
		 	&& DataForPBIX[Week] >= [Min of Week Selected]
    )
) 

Create a Delta measure that simply subtracts the latest week select OT% and the OT % of the entire selected range:

Delta = [Latest Week Selected %] -[Range Selected %]

Then thought creating a rank from that delta could be interesting:

Rank of Delta = 
RANKX(
		 ALL(DataForPBIX[DeptR]),
         [Delta],,
         DESC,
         Skip
) 

Table with all those (which may or may not be overkill to show...)

Building on that idea, created another measure that will be a table that shows only the Delta for Top "x"

TopN of Delta = 
IF(
	ISFILTERED( DataForPBIX[DeptR]),
	IF ([Rank of Delta] <= 10, [Delta],BLANK()))

So instead of seeing the entire table, only see the Top 10 in this case.  But could make it dynamic using the parameter/disconnected table method I used above:

There would still need to be further testing and such as there could be situations where the above formulas won't work.  All depends on the initial filter context really, especially for RANKX.  I do think it's a good step in the right direction. 

[Anonymous]

@Anonymous- Thank you very much for the detailed response.

After some testing I believe this is EXACTLY what I was looking for and I greatly appreciate the help!

[Anonymous]

Awesome! Glad it helped