Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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.
Solved! Go to 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
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