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,
It is ideal to have just one column for Region. In the Query Editor, select the first and last column, right click and then select "Unpivot other columns". Rename value column to Region. Create a relationship from the Region column of this table to the Dim Table. Create a Table visual and drag the column from the Dim Table. Write this measure
=SUM(Data[Tax])
Ashish_Mathur - Thanks for the response but the steps mentioned by you gives me mum = 75, whereas it should be 70 only however i did try the solution provided by v-piga-msft and it does work correctly.