Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Matrix - Display Row Data Only on Top Level of Hierarchy

Jump to Best Answer
  • 1.  Matrix - Display Row Data Only on Top Level of Hierarchy

    Bronze Contributor
    Posted Mar 14, 2019 02:55 PM
    Hi,

    Is there a way to display row data only on top level of hierarchy in a matrix visual?  Please See screen shot below.

    Columns CY LP Sales, PY LP Sales, CY Revenue and PY Revenue are displaying exactly as I want them to (no changes needed).  What I would really like is columns Total Allocation and Available Allocation values only display in the top level of the hierarchy (red circle) and not in each row below int he hierarchy.  In this example $1,100.  The total row is correct also, just don't need it displayed for level 2 and 3.  Hierarchy is Sales Rep Name -> Customer -> Invoice number. In addition, I am using a calculate filter on a second table to bring in Total Allocation.  It is a yearly set amount.  The Available Allocation column is CY LP Sales - Total Allocation.  Any help would be appreciated.

    Thanks,
    Tim

    ONqmhSIERjOXDOuXYkU2_Capture.JPG

    ------------------------------
    Tim Stutzman
    ERP Manager
    Eugene OR
    5412844711
    ------------------------------


  • 2.  RE: Matrix - Display Row Data Only on Top Level of Hierarchy
    Best Answer

    Posted Mar 15, 2019 05:21 AM
    Edited by Gopa Kumar Sivadasan Mar 15, 2019 12:11 PM
    Hi @Tim Stutzman

    Try using the ISINSCOPE function. In your measure for 'Total Allocation' and 'Available Allocation', use a conditional formula >> IF(ISINSCOPE(Table1[Sales Rep Name]),…give your calculation here…, BLANK()).


    ------------------------------
    Gopa Kumar S
    ------------------------------



  • 3.  RE: Matrix - Display Row Data Only on Top Level of Hierarchy

    Bronze Contributor
    Posted Mar 15, 2019 12:14 PM
    Hi @Gopa Kumar Sivadasan

    That worked perfectly!  Thanks for your help!

    Tim​

    ------------------------------
    Tim Stutzman
    ERP Manager
    Eugene OR
    5412844711
    ------------------------------



  • 4.  RE: Matrix - Display Row Data Only on Top Level of Hierarchy

    Silver Contributor
    Posted Mar 15, 2019 07:04 AM
    i think HASONEVALUE() will help you in that situation, together with BLANK()

    ------------------------------
    Lutz
    ------------------------------