Forum Discussion
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 |
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
johnt75Super 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?
- 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.