cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

Need help with dividing two different grouped value from different tables

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
1 ACCEPTED SOLUTION
Super User

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 )
)
``````
3 REPLIES 3
Super User

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 )
)
``````
New Member

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?

Super User

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.