The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
94 | |
80 | |
63 | |
56 |
User | Count |
---|---|
248 | |
122 | |
110 | |
77 | |
70 |