Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Kaiyu2023
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

 

TypeFees
a2
b3
b4
c2
c3
e5

Table 2

TypeAmount
a3
b6
b5
p9
q7
1 ACCEPTED SOLUTION
johnt75
Super User
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 )
    )

View solution in original post

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

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?

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.

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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