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 - 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

 

 

 

 

 

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.

4 Replies

  • 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.

  • PaulKraemer's avatar
    PaulKraemer
    Advocate II
    1 year ago

    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