Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello powerbi users,
I have a revenue table, expense table and calendar table
revenue table (date column) has a relationship with calendar table (date column)
expense table (date column) has a relationship with calendar table (date column)
and i have an inactive relationship between expense table (column item) and revenue table (column item)
example
Revenue table:
Date | Amount | Item |
11/20/2022 | 1000 | Item A |
11/20/2022 | 2000 | Item A |
11/20/2022 | 500 | Item B |
12/01/2022 | 1000 | Item C |
12/02/2022 | 3000 | Item C |
Expense Date:
Date | Amount | Item |
11/20/2022 | 500 | Item A |
11/20/2022 | 1500 | Item B |
11/20/2022 | 400 | Item B |
12/01/2022 | 750 | Item C |
12/02/2022 | 2000 | Item C |
i want to divide amount of revenue over amount of expenses so
Divide(sum(revenue[amount),sum(expense[amount]),0)
but i want it to divide over the item that are equal
so if i choose on my slicer 11/20/2022
amount of item A for revenue = 3000
amount for item A for expense = 500
Item | Price Per Product |
item A | 3000/500 = 6 |
any help would be appreciated
Solved! Go to Solution.
Hi @eliasayy ,
I have created a simple sample, please refer to it to see if it helps you.
Create a measure.
Measure =
VAR _1 =
CALCULATE (
SUM ( 'Table'[Amount] ),
FILTER (
ALL ( 'Table' ),
'Table'[Item] = SELECTEDVALUE ( Expense[item] )
&& 'Table'[Date] = SELECTEDVALUE ( Expense[Date] )
)
)
VAR _2 =
CALCULATE (
SUM ( Expense[Amount] ),
FILTER (
ALL ( Expense ),
Expense[Date] = SELECTEDVALUE ( Expense[Date] )
&& Expense[item] = SELECTEDVALUE ( Expense[item] )
)
)
RETURN
_1 / _2
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @eliasayy ,
I have created a simple sample, please refer to it to see if it helps you.
Create a measure.
Measure =
VAR _1 =
CALCULATE (
SUM ( 'Table'[Amount] ),
FILTER (
ALL ( 'Table' ),
'Table'[Item] = SELECTEDVALUE ( Expense[item] )
&& 'Table'[Date] = SELECTEDVALUE ( Expense[Date] )
)
)
VAR _2 =
CALCULATE (
SUM ( Expense[Amount] ),
FILTER (
ALL ( Expense ),
Expense[Date] = SELECTEDVALUE ( Expense[Date] )
&& Expense[item] = SELECTEDVALUE ( Expense[item] )
)
)
RETURN
_1 / _2
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
69 | |
35 | |
21 | |
18 | |
15 |
User | Count |
---|---|
127 | |
29 | |
27 | |
24 | |
22 |