How to manage a hierachycal link between tables

[alessio_missio]

Hi all, this is my first post, please be patient.

I need to build a Dataset that is giving me many problems.

I'm a newbie in Power BI that's why I'm here.

I've 2 tables:

1. Table TGT - Contains defined target by country, region, subregion
2. Table ORD - Containg Orders by country, region, subregion 

Let's have a look at the TGT table:

Country, TGT

Austria, 10

Germany, 40

Switzerland, 15

DACH, 80

and also the ORD table:

Country, ORD

Austria, 5

Austria, 1

Germany, 3

Germany, 10

Switzerland, 5

Please notice that I don't have any order with region DACH, and also please remember that DACH means Austria and Germany and Switzerland.

The problem is that I want to build a table with the following columns:

Country, Orders, TGT

There will be a FIlter by Region/Country and if the user will select Austria will get:

Country, Orders, TGT

Austria, 6, 10

for Germany:

Country, Orders, TGT

Germany, 13, 40

and so on... and here comes the tricky part, what will happen if someone will choose DACH ?

The output should be:

Country, Orders, TGT

DACH, 24, 80

Where 24 = 6+13+5 (sum of orders from Austria, Germany, Switzerland)

and 80 isn't the sum of the single targets by region, but is instead the value defined by region DACH.

So my measure should work this way: if I have a connection by lowest level (Country) use it, else use the upper layer (Subregion).

Please help me.

[v-piga-msft · Accepted solution]

Hi @alessio_missio,

You could create the measure or the calculated column in TGT table like below.

Measure = IF(MAX('TGT'[Country])="DACH",CALCULATE(SUM(ORD[ORD]),ALL(ORD)),CALCULATE(SUM(ORD[ORD])))

Column = IF('TGT'[Country]="DACH",CALCULATE(SUM(ORD[ORD]),ALL(ORD)),CALCULATE(SUM(ORD[ORD])))

Here is the output.

Best Regards,

Cherry