Can't determine relationship between fields - Different details

[HitcH]

Hello everyone,

I created a test simulation with two simple tables (+ two "bridge" table):

As you can see there are two common fields (Brand and Customer) + field "Bonus type" that is available only in sheet 1.

Here is my problem:

In my example I have, for brand "M" and customer "a", total bonus value of 100 (50 bonus type "A" and 50 bonus type "B") and turnover value of 99.

The system doesn't know how to allocate my turnover value between bonus type A and B and so I get the error "Can't determine relationship", but I would like to see in both case the total amount of turnover, like this:

In Excel this would be easy, is this also possible in PowerBI?

Thanks a lot!!!

[Anonymous]

Hi @HitcH ,

I have a test by your data model, I think it works well on myside.

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rbriga]

Don't use SUM to summarize your bonus and turnover. Try:

Total Bonus = 
    VAR _Unique_Table= SUMMARIZE('Test Table','Test Table'[Brand],'Test Table'[Customer], "@Bonus",MAX('Test Table'[Bonus]))
    RETURN 
    SUMX(_Unique_Table, MAX([@Bonus]))

[HitcH]

Another big "thanks" and another step ahead. But also another question:

Customer "a" and brand "M" has a total amount of bonus of 100 + brand "N" with extra 50. So the total "a" should be 150 and the total "a/M" should be 100:

Is there a problem with the formula?

[rbriga]

Try the equivalent of:

Total Bonus = 
    VAR _Unique_Table= 
    SUMMARIZE('Test Table',
        'Test Table'[Brand],
        'Test Table'[Customer], 
        "@Bonus",SUM('Test Table'[Bonus])
        )
    RETURN 
    SUMX(
        _Unique_Table, 
        MAX([@Bonus])
    )

I've changed the summary in SUMMERIZE to SUM.

[rbriga · Accepted solution]

How about merging (joining) the two tables in the Query Editor?

1. Choose the Sheet1 table
2. Merge Sheet 2 into it by brand, customer and bonus type
3. Expand Sheet1 with the column [Turnover] from Sheet2 

[HitcH]

Hi rbriga.

First of all thanks for your support!
I didn't even know the merge function, but I studied and implemented it, with partial success:

The two rows with the green note are fine, but the total for combination customer "a" and brand "M" is wrong, as the turnover is doubled.

Do you also know how to handle this?