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

How to display data in a Matrix

Jump to Best Answer
  • 1.  How to display data in a Matrix

    Posted Jan 28, 2021 04:00 PM
      |   view attached
    Hi All,

    I am trying to display data in the lowest level of hierarchy in my matrix visual, using SELECTEDVALUE, but it automatically groups the values to a higher level of the hierarchy. Please see the screenshot below (How can I remove/not display the yellow highlighted cells).

    I have also attached a sample .pbix.

    Would really appreciate any help.



    ------------------------------
    Murtaza Hasan
    Business Intelligence Specialist
    ------------------------------

    Attachment(s)

    pbix
    Sample File.pbix   233 KB 1 version


  • 2.  RE: How to display data in a Matrix

    Top Contributor
    Posted Jan 28, 2021 09:29 PM
      |   view attached
    Hi Murtaza:

    Please see attached. I hope this helps.

    Thanks.

    Bill S.


    ------------------------------
    William Skelley
    01106
    Longmeadow MA
    8602807221
    ------------------------------

    Attachment(s)

    pbix
    DisplayManager 1-28.pbix   233 KB 1 version


  • 3.  RE: How to display data in a Matrix

    Posted Jan 28, 2021 10:18 PM

    Hi @William Skelley,

    Really appreciate your reply!

    I want to actually remove the data from a higher level of the hierarchy. ​Data should only be displayed in front of Employee No. I want to remove the highlighted data, so that the details are only displayed corresponding to the employee no and not the department.



    ------------------------------
    Murtaza Hasan
    Business Intelligence Specialist
    ------------------------------



  • 4.  RE: How to display data in a Matrix

    Bronze Contributor
    Posted Jan 29, 2021 04:09 AM
      |   view attached

    Hi @Murtaza Hasan,
    The trick is to use 'isinscope' on 'employee ID'​ in the 2 measures to display employee & manager description. 



    ------------------------------
    Marc Schroyen
    Liège Belgium
    ------------------------------

    Attachment(s)



  • 5.  RE: How to display data in a Matrix

    Posted Jan 29, 2021 09:38 AM

    Hi @Marc Schroyen,

    That is absolutely brilliant. I have been looking for a solution for weeks now. Thank you so much!​​​



    ------------------------------
    Murtaza Hasan
    Business Intelligence Specialist
    ------------------------------



  • 6.  RE: How to display data in a Matrix

    Posted Mar 03, 2021 11:20 AM
    Edited by Murtaza Hasan Mar 03, 2021 04:32 PM
      |   view attached

    Hi @Marc Schroyen

    There is another issue, if you could help, please.

    In the solution that you attached, I also want to retain the total values for the first and second levels of the hierarchy (for the salary column). For example, the Salary total for a Company or the department level should remain the same, even when the slicer for Company, Department, or Employee is selected.


    ​In the above screenshot, the total salary for Company-A is 555,000 and for Finance Department is 225,000 but once I use slicers, the total also changes.

    The below screenshot is with Company and Department slicer selected:

    The below screenshot is with Company, Department, and employee slicer selected:


    (Edited, as I figured out most of the part)

    I tried to write DAX, as below but it partially worked. It works for Company and Department level, but as soon as I select an Employee, the total against the department changes. I used ALL filter in the last to achieve this but it's not working.

    Salary Test =
    IF (
    ISINSCOPE ( Sheet1[Employee ID] ),
    SELECTEDVALUE ( Sheet1[Salary] ),
    IF (
    ISINSCOPE ( Sheet1[Department]),
    CALCULATE (
    SUM(Sheet1[Salary]),
    FILTER (
    Sheet1,
    Sheet1[Company]
    = SELECTEDVALUE ( Sheet1[Company] )
    ),
    FILTER (
    Sheet1,
    Sheet1[Department]
    = SELECTEDVALUE ( Sheet1[Department] )
    )
    , ALL(Sheet1[Employee ID])
    ),
    CALCULATE (
    SUM(Sheet1[Salary]),
    ALLEXCEPT ( Sheet1, Sheet1[Company] )
    )
    )
    )

    I have also attached the updated file again for your convenience. Really appreciate your help. Thank you.



    ------------------------------
    Murtaza Hasan
    Business Intelligence Specialist
    ------------------------------

    Attachment(s)

    pbix
    Matrix Visual Sample.pbix   21 KB 1 version


  • 7.  RE: How to display data in a Matrix
    Best Answer

    Bronze Contributor
    Posted Mar 04, 2021 10:30 AM
      |   view attached

    Hi @Murtaza Hasan,

    To replace total value on different level, you need to remove the filters and apply the new total.
    Here is the code:

    Salary 2 = 
    // build temporary table, removefilter for total
    VAR Department =
        CALCULATETABLE ( Sheet1, REMOVEFILTERS ( Sheet1[Employee ID] ) )
    VAR Company =
        CALCULATETABLE (
            Sheet1,
            REMOVEFILTERS ( Sheet1[Department], Sheet1[Employee ID] )
        )
    VAR Total =
        CALCULATETABLE (
            Sheet1,
            REMOVEFILTERS ( Sheet1[Company], Sheet1[Department], Sheet1[Employee ID] )
        )
    VAR EmployeeSalary = CALCULATE ( SUM ( Sheet1[Salary] ) )
    VAR DepartmentSalary =
        CALCULATE ( SUMX ( Department, Sheet1[Salary] ) )
    VAR CompanySalary =
        SUMX ( Company, Sheet1[Salary] )
    VAR TotalSalary =
        CALCULATE ( SUMX ( Total, Sheet1[Salary] ) )
    RETURN
        SWITCH (
            TRUE (),
            ISINSCOPE ( Sheet1[Employee ID] ), EmployeeSalary,
            ISINSCOPE ( Sheet1[Department] ), DepartmentSalary,
            ISINSCOPE ( Sheet1[Company] ), CompanySalary,
            TotalSalary
        )
    






    ------------------------------
    Marc Schroyen
    Liège Belgium
    ------------------------------

    Attachment(s)

    pbix
    Matrix Visual Sample.pbix   25 KB 1 version


  • 8.  RE: How to display data in a Matrix

    Posted Mar 04, 2021 11:04 AM

    @Marc Schroyen

    It worked! Thank you so much!​



    ------------------------------
    Murtaza Hasan
    Business Intelligence Specialist
    ------------------------------