Forum Discussion
calculating sum based on two columns having same data
Hi Anonymous,
To achieve your desired output, you could follow the steps.
1. Fo to Query editor, duplicate your table and unpivot the column region 1 and region 2, remove the Attribute column and rename the Value to Region, then Apply and Close.
2. Create the relationship between the duplicated table and Dim table with the column Region, and then create the measure with the formula below.
Measure = VAR _table = SUMMARIZE ( 'Table1 (2)', 'Table1 (2)'[ID], 'Table1 (2)'[Value], "_tax", AVERAGE ( 'Table1 (2)'[Tax] ) ) RETURN SUMX ( _table, [_tax] )3. Here is the output.
You also could have a reference of the attachment below.
Best Regards,
Cherry
Hi Anonymous,
To achieve your desired output, you could follow the steps.
1. Fo to Query editor, duplicate your table and unpivot the column region 1 and region 2, remove the Attribute column and rename the Value to Region, then Apply and Close.
2. Create the relationship between the duplicated table and Dim table with the column Region, and then create the measure with the formula below.
Measure =
VAR _table =
SUMMARIZE (
'Table1 (2)',
'Table1 (2)'[ID],
'Table1 (2)'[Value],
"_tax", AVERAGE ( 'Table1 (2)'[Tax] )
)
RETURN
SUMX ( _table, [_tax] )
3. Here is the output.
You also could have a reference of the attachment below.
Best Regards,
Cherry
v-piga-msft - If i have another column similar to region 1 and region 2 i.e. department 1 and department 2 , i believe it works as expected , correct ? . Also yesterday i tried something similar but in query editor see the below screen shot
And it worked as expected, let me know whether this is also a corerct approach