Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
eliasayy
Impactful Individual
Impactful Individual

Divide 2 tables by inactive relationship

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/20221000Item A
11/20/20222000Item A
11/20/2022500Item B
12/01/20221000Item C
12/02/20223000Item C


Expense Date:

Date               Amount    Item
11/20/2022500Item A
11/20/20221500Item B
11/20/2022400Item B
12/01/2022750Item C
12/02/20222000Item 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 A3000/500 = 6

 

any help would be appreciated

1 ACCEPTED SOLUTION
v-rongtiep-msft
Community Support
Community Support

Hi @eliasayy ,

I have created a simple sample, please refer to it to see if it helps you.

vpollymsft_0-1670377777113.png

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

vpollymsft_2-1670377861694.png

 

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.

 

View solution in original post

1 REPLY 1
v-rongtiep-msft
Community Support
Community Support

Hi @eliasayy ,

I have created a simple sample, please refer to it to see if it helps you.

vpollymsft_0-1670377777113.png

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

vpollymsft_2-1670377861694.png

 

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors