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.
I have 2 data tables that are linked through 3 master data tables.
Table one:
Period - Company Code - Profit Center - Sales Amount
Table two:
Period - Company Code - Profit Center - Net assets amount
I like to add the summary sales from table one to table 2, but it should only match for period and company code and ignore the link for Profit center.
I tried with calculate(sum(table1[sales amount]),allexcept(table2[Period],table2[Company code]))
but this doesn't work.
Any ideas?
Solved! Go to Solution.
Figured it out:
Thank you
= CALCULATE ( SUM ( Table1[Sales Amount] ), FILTER ( ALL ( Table1), Table1[Company Code] = Table2[Company Code] ), FILTER ( ALL ( Table1), Table1[Period] = Table2[Period] ) )
try this as a calculated column in Table2
= CALCULATE ( SUM ( Table1[Sales Amount] ), FILTER ( ALL ( Table1[Company Code] ), Table1[Company Code] = Table2[Company Code] ), FILTER ( ALL ( Table1[Period] ), Table1[Period] = Table2[Period] ) )
Figured it out:
Thank you
= CALCULATE ( SUM ( Table1[Sales Amount] ), FILTER ( ALL ( Table1), Table1[Company Code] = Table2[Company Code] ), FILTER ( ALL ( Table1), Table1[Period] = Table2[Period] ) )
Thank you, but it didn't work.
It didn't ignore the link on the profit center. If I would have 5 rows with different profit centers for a specific period and a specific company code, I want all 5 lines to get the same answer. In table 1 there is more than one row for a specific period and a specific company code. Those need to be added together and put on all 5 lines in table 2.
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |