Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
Novice asking again beginner questions … Thanks in advance for your help
There are 2 tables, TableResult and TableTarget
TableResult | |||
Region | Profit_Center | Revenue | Region_Target |
DE | PC1 | 100 | 1500 |
DE | PC2 | 100 | 300 |
NL | PC1 | 200 | 700 |
… | ... | ... | … |
TableTarget | ||
Region | Profit_Center | Revenue |
DE | PC1 | 1000 |
DE | PC1 | 500 |
DE | PC2 | 300 |
NL | PC1 | 700 |
Scenario:
Column Region_Target in TableResult needs to be calculated, using 2 tables.
Region_Target = Sum of Revenue in TableTarget with Filter using Profit_Center and Region from TableResult.
Following would give me the correct value for Region_Target in row1:
CALCULATE(SUM(TableTarget[Revenue]);FILTER(TableTarget;TableTarget[Region]="DE";TableTarget[Profit_Center]="PC1"))
But obviously I want to refernce to Profit_Center and Region in TableResult to calculate automatically for each row.
I tried with SUMX and CALCULATETABLE but did not come to any useful result. An ALLEPECXT needs to go in there as well I guess.
Any advice?
Solved! Go to Solution.
Hi @BobbyDollar
Region_Target = CALCULATE ( SUM ( TableTarget[Revenue] ); FILTER ( TableTarget; TableTarget[Region] = TableResult[Region] && TableTarget[Profit_Center] = TableResult[Profit_Center] ) )
Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.
Cheers
Hi @BobbyDollar
Region_Target = CALCULATE ( SUM ( TableTarget[Revenue] ); FILTER ( TableTarget; TableTarget[Region] = TableResult[Region] && TableTarget[Profit_Center] = TableResult[Profit_Center] ) )
Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.
Cheers
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |