Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

calculating sum based on two columns having same data

Hi All,

 

I am finding it difficult to handle a scenario which is as follows - 

 

Table 1 is as below, I would like to calculate the sum(tax) based on the values in region 1 and region 2 column i.e. 

 

Measure = sum(tax) where region 1 = 'region1' or region 2 = ' region 2'. So the output should be Example : Mum = 70

 

Dim table  =

 

 

Kindly request you alll to provide your suggestions

Need Help
Show and Tell
Tips and Tricks
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    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

8 Replies

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Hi Anonymous

     

    Try this:

     

    1. Place a matrix visual on your report

    2. Set Region from your fact table in the rows of the matrix

    3. Set this measure in values

     

    Measure =
SUMX ( Table1,
       IF ( FIND ( SELECTEDVALUE ( DimTable[Region] ),
                   Table['region 1'] & Table1['region 2'],
                   1,
                   0
                  ) > 0,
            Table1[Tax],
            0
        )
      )

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks AlB - Would also  like to know how it would work if i need to include another condition of department 1 and department 2 as well.

      • AlB's avatar
        AlB
        Community Champion
        7 years ago

        Anonymous

         

        What condition exactly?

        You could just add it to the logical test in the IF (first argument).

         

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    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])

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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.