Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |