Forum Discussion
Need help with dividing two different grouped value from different tables
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 ) )
Super UserFirst 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.