Entering the (Power BI) Matrix – Part 3: Exceptions and Indicators

So what do you need? Besides a miracle? – Tank
Guns. Lots of Guns. – Neo

Last time around, we investigated DAX techniques to dynamically handle unit scaling, which is useful where values differ significantly in various slices of a matrix. This time around, we’ll use similar logic to handle exceptions and indicators for values in calculated columns.

The formula for deriving variance versus the prior year is best calculated as:

This formulation will handle negative values in the prior year, which we know are possible in this dataset. It is also possible that we will have null (or zero) input for the prior year, so we’ll need to handle that as well.

The / operator in DAX is not protected from divide by 0. It will produce “NaN” (Not a Number) as output when the denominator is 0 in this operation. This may or may not break output for the calculation itself, but can definitely impact any further calculations based on this output.

For this reason, it is advisable to always use the divide function in your DAX logic instead. Syntax for this function is:

So, in most rudimentary form, we can handle the basic exceptions by the following function:

Which will return null where the previous year values are 0 or null. However, our friends in The Matrix will need more from us than just that.

Our individual extract yields (for any given day/crewmember) are HIGHLY variable, ranging from negative gigabytes to positive petabytes. Given that these yields are generated by a random function, they tend to average out to more consistent values over aggregations, but the individual yields are all over the place.

And again, no one in the C-Suite in Zion (Who else is consuming this Reporting?) is going to want to compare 869329843.07% with -24.37%. We’ll need to do better, to help them make good business decisions.

For values from -999.99% to 999.99%, numeric values make sense. When dealing with production data, comparisons that exceed this range are often because the comparison itself is not useful, for one reason or another. In actual use cases for clients, we’ve often gone with an exception indicator such as “>999%” for values that exceed this scale.

But that’s not always the case. The requirements of your reporting may find otherwise. So let’s assume our friends in the Matrix are interested in those large variances, for the sake of the exercise.

Here’s a stab at handling any value our variance calculation will produce:

Click to Copy

variance_display = VAR CY = SUM ( fact_matrix[kbytes] ) VAR PY = CALCULATE ( SUM ( fact_matrix[kbytes] ) * .85, SAMEPERIODLASTYEAR ( dim_date[Date] ) ) VAR varpct = DIVIDE ( cy – py, ABS ( py ) ) RETURN SWITCH ( TRUE (), CY = BLANK (), BLANK (), PY = BLANK (), “‽ ‽ ‽”, VARPCT >= 99999.99, “△△△△△”, VARPCT >= 9999.99, “△△△△”, VARPCT >= 999.99, “△ △ △”, VARPCT >= 99.99, “△ △”, VARPCT >= 9.99, “△”, VARPCT <= -99999.99, “▽▽▽▽▽”, VARPCT <= -9999.99, “▽▽▽▽”, VARPCT <= -999.99, “▽ ▽ ▽”, VARPCT <= -99.99, “▽ ▽”, VARPCT <= -9.99, “▽”, FORMAT ( VARPCT, “#.0#%” ) )

This function performs the following:

• store the aggregated sum of our random kbytes column in the variable CY
• store the aggregated sum of the prior years kbytes column (with an adjustment factor, to weight our random values toward annual growth) in the variable CY
• compute the variance value
• if CY is null, output null
• if PY is null, output “‽‽‽”. Because Interrobang!
• if the variance exceeds 10m percent positive, output 5 unicode upward triangles “△”
• for each power of ten less, output one less “△”
• For negative values, perform the equivalent with downward triangles “▽”
• For values falling between -999% and 999%, output the numeric values.

Handling and showing these large variances begs the question though: Why are they so large? Which is perhaps indicative of good reporting – the best answers should always prompt more questions, right?

In this case, it might be useful to consider not just the scale of the variance, but the scale of the difference between the two years as well.

And so we have a similar function for difference, like so:

Click to Copy

difference_display = VAR CY = SUM ( fact_matrix[kbytes] ) VAR PY = CALCULATE ( SUM ( fact_matrix[kbytes] ) * .85, SAMEPERIODLASTYEAR ( dim_date[Date] ) ) VAR difference = cy – py RETURN SWITCH ( TRUE (), difference >= 2 ^ 50, “▲▲▲▲▲”, difference >= 2 ^ 40, “▲▲▲▲”, difference >= 2 ^ 30, “▲ ▲ ▲”, difference >= 2 ^ 20, “▲ ▲”, difference >= 2 ^ 10, “▲”, difference <= -2 ^ 50, “▼▼▼▼▼”, difference <= -2 ^ 40, “▼▼▼▼”, difference <= -2 ^ 30, “▼ ▼ ▼”, difference <= -2 ^ 20, “▼ ▼”, difference <= -2 ^ 10, “▼”, difference = BLANK (), BLANK (), FORMAT ( difference, “### Kb” ) )

In this calculation, we don’t need to handle for /0, nor do we need to handle for null values for CY. A null + A null is still null, and we handle that in the function. All other cases are output.

And here’s the report page embedded from Power BI: