How to deal with blanks in hierarchies?

[LP135]

I have data for two years (later: 50 years) which I structure in a three (later: five) level hierarchy. The hierarchy is defined in a separate table and then connected to the actual data via a relationship.

For the second year, the data is forecasted - however, only for Levels 1 and 2, so level 3 (later: 3-5) is blank, i.e., in the forecast, we do not care about the level-3-composition of level 2, we just forecast the aggregate. This forecast is done externally, not in the Power BI model.

Since the hierarchy leads to automatic aggregation of lower levels to higher levels, the forecast of level 2 is not shown in the resulting matrix and the total (level 1) is consequently wrong.

How can I tell Power BI that it should use the "hard", i.e. manually implemented, values for levels 1 and 2 if no values are available for level 3?

I have uploaded an example file here:

Uploaded example file

Any help is highly appreciated!

[v-yulgu-msft]

Hi @LP135,

"How can I tell Power BI to use the value for NL 2017 if Health 2017 and/or P&C 2017 are not available?"

I think it doesn't make sense to achieve such a result. 

The relationship is generated based on Table1[Attribute] and Table2[3]. That is to say, each item of Table1[Attribute] is matched with that in Table2[3]. The record "2017 PI NL 3" doesn't find its mapping row in Table2[3], that is why no value is displayed in Matrix under level-3. Also, the "PI NL" we can see in Matrix is matched with items in Table2[2] instead of Table2[3]. So, it is not possible to refer to the record "2017 PI NL 3" in Table1 using current relationship (Table1[Attribute] <--> Table2[3]).

In your scenario, you may need to insert below record to Table2.

1               2            3

PI Total     PI NL      PI NL

Best regards,

Yuliana Gu