Matrix Visualization with multiple grouping levels - applying a filter to just one level?

[PaulKraemer]

Hi,

I have created a Matrix Visualization that looks like the image below.... 

The Data Source is a table that has three fields that I use for grouping and summarization - TenantTag, tik_num, and stock_num.  I have linked these fields to Rows in the Matrix Visualization.  The remaining fields in this table hold the values that I summarize - ActFootage, EstFootage, est_footage_qty_adjust, and total_footage_scanned.  I have linked these fields to Values in the Matrix Visualization.

Generally, the value in total_footage_scanned should be very close (within +/- 10%) to the value in est_footage_qty_adjust.  My hope for this Visualization is to make it easy to identify cases where this does not hold true.  

There will be cases where at the stock_num level, this +/- 10% comparison might indicate a potential problem, but looking at the tik_num level would make it obvious that this discrepancy is easily explainable.  (Within one tik_num, we might find that one stock_num was scanned 25% more than expected, but this could be offset by another stock_num that was scanned 25% less than expected, resulting in an even match at the tik_num level.)

With this being possible, I'd like to find a way that would allow me to filter this matrix with the following two user-selectable options:

1. Only include records for which the +/- 10% comparison fails at the stock_num level
2. Only include records for which the +/- 10% comparison fails at the tik_num level

I am fairly new to Power BI.  If anyone here can recommend a general approach I can take to solve a challenge like this (or point me towards an example), I would greatly appreciate it.

Thanks in advance,
Paul

[PaulKraemer]

Thank you @danextian  !  That works great !! 

To take this one step further, rather than hard-coding the filter for +/- 10%, is there any way that I could use a user adjustable variable for this percentage?  For example, can I change the following measure ...

 Filter - tik_num = IF ( ABS ( [Difference - tik_num] ) <= 0.1, 1, 0 ) 

...... to something like ...

Filter - tik_num = IF ( ABS ( [Difference - tik_num] ) <= MyVariable, 1, 0 )

... and then provide an entry field that allows the user to change the value in MyVariable?

I really appreciate your help!

Thanks again and best regards,
Paul

[danextian]

Hi @PaulKraemer 

You will need to write at least two measure - one that returns the % difference by stock_num_level and the other one by tik_num level and use a disconnected table for a possibly a field parameter to switch betwen the two. But as always, please provide a workable sample data (not an image) and your expected result from the same sample data. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.

[PaulKraemer]

Hi @danextian -

Thank your for your reply.  I exported some of the underlying data to an Excel file and recreated my Matrix Visualization in a new .pbix file for the purpose of example.  If I did it correctly, here is a link to it on my OneDrive...

ScannedVsExpected.pbix

.. In my Matrix Visualization, I will use TenantTag = Luminer, tik_num = 5412 as an example for what I am hoping to accomplish.  

stock_num	Sum of est_footage_qty_adjust	Sum of total_footage_scanned
P1139	1,129	2,129
P2065	1,129	129
Total	2,258	2,258

In this example, the Sum of total_footage_scanned is 88% higher than the sum of est_footage_qty_adjust for stock_num P1139 AND 88% lower for P2065, but the Totals are the same.  In cases like this, if I filtered for percentage difference outside of the +/- 10% at the tik_num grouping level, this tik_num should not be flagged as a "problem".  If I filtered for percentage difference outside of the +/- 10% at the stock_num grouping level, both P1139 and P2065 should be flagged as a "problem".

I appreciate your help.

Thanks again!

Paul

[danextian · Accepted solution]

Hi @PaulKraemer 

You'll need to compute the percentage difference at both the stock_num and tik_num level.

Difference - stock_num = 
VAR _scanned =
    SUM ( ScannedVsExpected[total_footage_scanned] )
VAR _qty =
    SUM ( ScannedVsExpected[est_footage_qty_adjust] )
VAR _diff = _scanned - _qty
RETURN
    DIVIDE ( _diff, _qty )

Difference - tik_num = 
VAR _scanned =
    CALCULATE (
        SUM ( ScannedVsExpected[total_footage_scanned] ),
        ALLEXCEPT ( ScannedVsExpected, ScannedVsExpected[tik_num] )
    )
VAR _qty =
    CALCULATE (
        SUM ( ScannedVsExpected[est_footage_qty_adjust] ),
        ALLEXCEPT ( ScannedVsExpected, ScannedVsExpected[tik_num] )
    )
VAR _diff = _scanned - _qty
RETURN
    DIVIDE ( _diff, _qty )

Then write measures as visual filter based on the result of the above measures

Filter - stock_num = 
IF ( ABS ( [Difference - stock_num] ) <= 0.1, 1, 0 )

Filter - tik_num = 
IF ( ABS ( [Difference - tik_num] ) <= 0.1, 1, 0 )

Create a placeholder table/disconnected table to be able to switch between these two filter measures

Final Filter = 
VAR _comparison =
    SELECTEDVALUE ( ComparisonOption[Sort] )
VAR _both = [Filter - tik_num] = 1
    || [Filter - stock_num] = 1
RETURN
    SWITCH (
        TRUE (),
        _comparison = 1, [Filter - stock_num],
        _comparison = 2, [Filter - tik_num],
        _both, 1
    )

The  percentages and the filter measuers don't need to be in the visual. They're there to easily visualize the results.

Pelase see the attached sample pbix.