Matrix : hide lowest level rows

Question

Hi all,&nbsp;I have Organization Hierarchy of for instance 4 levels. Each organization unit has a type (Region or location). Regions can have subregions or locations. Locations can have sublocations (no subregions).&nbsp;Only locations can fill in checklists. In the datamodel I have a table for the organizationunits and a table for the checklists. In the checklist table I have defined several measures.&nbsp;We want to create matrix where we can see all regions/subregions but not the locations. In this matrix I want to show the calculated values of the different measures.&nbsp;When I create a matrix on the complete Organization Unit table and show the different measures and I filter out all organizationunits which are a location, then no values are shown for the matrixI leave out the filter, the following is shownIs it possible to show the image above, with without the "+" sign?&nbsp;The datamodel looks as followsWhere this table contains the following dataThe fact table looks like thiswith the folllwing contentA few measures:TotalCompleted = VAR val = CALCULATE(COUNTROWS(FACT_PublishedChecklist), dim_qualitystatus[Status] = "Completed")VAR EntityShowRow =&nbsp; &nbsp; [EntityBrowseDepth] &lt;= [EntityRowDepth]VAR Result =&nbsp; &nbsp; IF ( EntityShowRow, Val )return Result&nbsp;TotalWithExceptions = VAR val = CALCULATE(COUNTROWS(FACT_PublishedChecklist), DIM_QualityStatus[Status] = "Completed with exceptions")VAR EntityShowRow =&nbsp; &nbsp; [EntityBrowseDepth] &lt;= [EntityRowDepth]VAR Result =&nbsp; &nbsp; IF ( EntityShowRow, Val )return Result&nbsp;&nbsp;Total = VAR val = COUNTROWS(fact_publishedchecklist)VAR EntityShowRow =&nbsp; &nbsp; [EntityBrowseDepth] &lt;= [EntityRowDepth]VAR Result =&nbsp; &nbsp; IF ( EntityShowRow, Val )return Result&nbsp;&nbsp;CompletionScore = ([TotalCompleted]+[TotalWithExceptions])/[Total]

greg_deckler · Answer

DirkVr&nbsp;Can you provide an example of how your actual data looks?&nbsp;Sorry, having trouble following, can you post sample data as text and expected output?Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490The most important parts are:1. Sample data as text, use the table tool in the editing bar2. Expected output from sample data3. Explanation in words of how to get from 1. to 2.

dirkvr · Answer

I have added some additional info in my original post

greg_deckler · Answer

DirkVr&nbsp;Thanks, that is helpful. So, if I am understanding things, sometimes the Level 2 might be location and sometimes it might be subregion? Is that correct?

dirkvr · Answer

That is correct

greg_deckler · Answer

DirkVr&nbsp;There might be the answer to the issue. Your hierarchy is not what I would call a "proper" hierarchy. Would it be possible to reformat your data into a proper hiearchy where Level 1 is always Region, Level 2 always Subregtion, Level 3 ??? and Level 4 is Location?
&nbsp;
Then you could simply put Level 1 and Level 2 into your matrix as an informal hierarchy and the problem should be solved.

Forum Discussion

Matrix : hide lowest level rows

7 Replies

Related content

KPI level matrix table

Matrix Visual Hierarchy Level Problem

Only show values in matrix for the 'lowest level'

Show column on matrix's lowest level only

Matrix visual not expanding new rows to the lowest level after data refresh