Forum Discussion
Matrix Visualization with multiple grouping levels - applying a filter to just one level?
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.
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...
.. 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
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.