5 Brutal Truths: Your Power BI Drillthrough Still Doesn’t Show Root Cause

Read time: 2.5 minutes

5 Hard Truths About Power BI Root Cause Analysis

1. A Zoomed-Out View Doesn't Give You Your Root Cause
Drillthroughs can often produce many of the same visuals, but at a lower granularity than the original.
Solution: Replace drill tables with a Decomposition Tree and include measures of - Variance vs. Plan, Variance %, and Force Driver Contribution % on every drill page.

2. Your Model Is Not Explaining Your Variance
The flat design of your model obfuscates or dilutes the drivers of variance.
Solution: Enforce a Standard Star Schema by

• Separating driver dimensions of Channel, Region, and Product,

• Removing bi-directional relationships, and

• Using Calculation Groups for time-based comparisons of measures.

3. You Didn't Create Any Variance Calculation
There is no explanation or story to tell because the absolute value of the measures does not document a change.
Solution: DAX needs to include three calculations using the following: [Variance vs. Plan], [Variance %], and Driver Contribution %. For each of these calculations, all models must use “ALL()”.

4. There is No Context of a Business Executive
Users get to your report and ask, “So what?”
Solution: Create a dynamic narrative card containing, “–$1.2M caused by Retail (–62%).” Use SELECTEDVALUE() to automate the narrative of that transaction.

5. It Is Taking Too Long For Users to Find Out
If it takes more than 6 seconds to drill through, your user will abandon the drill.
Solution: Use the Performance analyser to optimize your DAX using variables, remove any unused columns, aggregate large Fact Tables, and enable Incremental Refresh.

💡Key Takeaway: 

Dashboards show numbers but a problem’s root cause usually needs structure, math and narrative. If you can’t explain the difference between two values (delta) in one simple answer via your drillthrough, then you’re not finished with your analysis.