Divide 2 tables by inactive relationship

[Anonymous]

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

[Anonymous · Accepted solution]

Hi @Anonymous ,

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.