Forum Discussion

PaulKraemer's avatar
PaulKraemer
Advocate II
1 year ago
Solved

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

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 - TenantT...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    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.

danextian's avatar
danextian
Super User
1 year ago

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's avatar
PaulKraemer
Advocate II
1 year ago

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_numSum of est_footage_qty_adjustSum of total_footage_scanned
P11391,1292,129
P20651,129129
Total2,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's avatar
    danextian
    Super User
    1 year ago

    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.