Need help with dividing two different grouped value from different tables

[Anonymous]

Hi everyone,

I'm fairly new to the powerbi and hope that someone could share his/her wisdom to help me solve the problem.

The basic requirement for the task is that the aggregated fees grouped by type in table 1 will be divided by the aggregated amount grouped by type in table 2. For eample, in a new matrix, type a =2/3 and b = (3+4)/(6+5). However, any type that cannot create a relationship between two tables should be 0 in a new matrix. In this case Type = c/e/p/q is 0

One of the tricky part of the question is that the types in table 1 aren't the exact same as the types in table 2 so it cannot easily create a relationship between the two tables. 

I'd be much appreciated if someone can help me with that.

Table 1

Type	Fees
a	2
b	3
b	4
c	2
c	3
e	5

Table 2

Type	Amount
a	3
b	6
b	5
p	9
q	7

[johnt75 · Accepted solution]

First create a new type dimension table like

Type dimension =
DISTINCT ( UNION ( DISTINCT ( 'Table1'[Type] ), DISTINCT ( 'Table2'[Type] ) ) )

and link that to both tables. Make sure to use the column from that table in your visuals.

You can then create a measure like

New measure =
VAR Tab1Value =
    SUM ( 'Table1'[Fees] )
VAR Tab2Value =
    SUM ( 'Table2'[Amount] )
RETURN
    IF (
        ISBLANK ( Tab1Value ) || ISBLANK ( Tab2Value ),
        0,
        DIVIDE ( Tab1Value, Tab2Value )
    )

[Anonymous]

Thanks for your response. The Type dimension works perfectly in this case, but I'm not sure I fully understand how you create the measure. In my understanding, I would create a measure for each Sum calculation, and then establish the Divide function for the results.  Do you mind explaining what's the purpose of using VAR(I cannot find a related function in power BI) and Return?

[johnt75]

The VAR function allows you to create a variable ( actually a constant, but never mind that for now ) in which you can store the results of a calculation, either a table or a scalar value. Variables are really powerful and can improve performance because if you want to refer to the results of a calculation more than once then you don't need to reperform the calc, you can just reference the variable.

The return statement is needed whenever you create a variable.

You can find more about it all at dax.guide along with a short video.