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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors