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 two tables seperate tables shown below. An expense table and a revenue table. Both tables have a relationship based on the circuit column. I would like to create a measure that calculates the total Revenue when
Expense does not = 0
and Max Month = December
and Min Month = January
and Identifier = Y
Expense Table
| |||
Circuit | Min Month | Max Month | Expense |
A | January | December | 0 |
B | January | January | 10 |
C | January | December | 0 |
D | January | December | 20 |
E | April | June | 0 |
F | April | December | 40 |
G | April | December | 50 |
H | January | April | 10 |
I | January | May | 20 |
J | January | December | 30 |
K | January | December | 40 |
L | January | July | 50 |
M | January | December | 40 |
Revenue Table | ||
Circuit | Revenue | Identifier |
A | 100 | Y |
B | 120 | Y |
C | 200 | Y |
D | 300 | Y |
E | 80 | Y |
F | 100 | Y |
G | 20 | Y |
H | 40 | Y |
I | 60 | Y |
J | 100 | Y |
K | 200 | Y |
L | 20 | Y |
Solved! Go to Solution.
@dw700d Well, that makes a big difference. Try this:
Measure =
VAR __Table = FILTER('Expense', [Expense] <> 0 && 'Expense'[Max Month] = "December" && 'Expense'[Min Month] = "January")
VAR __Circuits = DISTINCT(SELECTCOLUMNS(__Table, "__Circuits", [Circuit]))
VAR __Result = SUMX(FILTER('Revenue', [Identifier] = "Y" && [Circuit] IN __Circuits),[Revenue])
RETURN
__Result
@dw700d Try:
Measure =
VAR __Table = FILTER( 'Revenue', [Identifier] = "Y" && RELATED(Expense[Expense]) <> 0 && RELATED(Expense[Max Month]) = "December" && RELATED(Expense[Min Month]) = "January")
VAR __Result = SUMX(__Table, [Revenue])
RETURN
__Result
@Greg_Deckler thank you. When I enter the related function intellisense does not give me an option to select a column. The revenue table has unique values and the expense table is the many side of the relationship. Maybe thats why but I am not sure. Any other thoughts?
@dw700d Well, that makes a big difference. Try this:
Measure =
VAR __Table = FILTER('Expense', [Expense] <> 0 && 'Expense'[Max Month] = "December" && 'Expense'[Min Month] = "January")
VAR __Circuits = DISTINCT(SELECTCOLUMNS(__Table, "__Circuits", [Circuit]))
VAR __Result = SUMX(FILTER('Revenue', [Identifier] = "Y" && [Circuit] IN __Circuits),[Revenue])
RETURN
__Result
Hello @dw700d ,
You can try the below solution. Please upvote if it works for you.
Total Revenue = CALCULATE( SUM(Revenue[Revenue]), Expense[Expense] <> 0, Revenue[Identifier] = "Y", DATESBETWEEN( Revenue[Circuit], FILTER( Expense, Expense[Min Month] = "January" ), FILTER( Expense, Expense[Max Month] = "December" ) ) )
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 |
---|---|
105 | |
97 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |