Matrix : hide lowest level rows

[DirkVr]

Hi all,

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

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. 

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. 

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 matrix

I leave out the filter, the following is shown

Is it possible to show the image above, with without the "+" sign?

The datamodel looks as follows

Where this table contains the following data

The fact table looks like this

with the folllwing content

A few measures:

TotalCompleted =

VAR val = CALCULATE(COUNTROWS(FACT_PublishedChecklist), dim_qualitystatus[Status] = "Completed")

VAR EntityShowRow =

    [EntityBrowseDepth] <= [EntityRowDepth]

VAR Result =

    IF ( EntityShowRow, Val )

return Result

TotalWithExceptions =

VAR val = CALCULATE(COUNTROWS(FACT_PublishedChecklist), DIM_QualityStatus[Status] = "Completed with exceptions")

VAR EntityShowRow =

    [EntityBrowseDepth] <= [EntityRowDepth]

VAR Result =

    IF ( EntityShowRow, Val )

return Result

Total =

VAR val = COUNTROWS(fact_publishedchecklist)

VAR EntityShowRow =

    [EntityBrowseDepth] <= [EntityRowDepth]

VAR Result =

    IF ( EntityShowRow, Val )

return Result

CompletionScore = ([TotalCompleted]+[TotalWithExceptions])/[Total]

[Greg_Deckler]

@DirkVr Can you provide an example of how your actual data looks? 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/1116882

Also, 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/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[DirkVr]

I have added some additional info in my original post

[Greg_Deckler]

@DirkVr 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]

That is correct

[Greg_Deckler]

@DirkVr 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?

Then you could simply put Level 1 and Level 2 into your matrix as an informal hierarchy and the problem should be solved.

[DirkVr]

That is an issue. I am working in a multi-tenant environment where each tenant has its own hierarchy structure. Some tenants have 1 region and below that several locations. Other tenants have 1 region, subregions. Below subregions, that have locations and sublocations.

[DirkVr]

Hi Greg,

You inspired me :-). I am working now a solution where I have 2 hierarchies. 1 for the regions, and 1 for the locations. I hope this will help 🙂