Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Table 1.PNG

 

Dim table  =

 

dim table.PNG

 

Kindly request you alll to provide your suggestions

1 ACCEPTED SOLUTION
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

 

result.PNG

 

You also could have a reference of the attachment below.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@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.

v-piga-msft
Resident Rockstar
Resident Rockstar

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.

 

result.PNG

 

You also could have a reference of the attachment below.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@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 screenshot.PNG

 

And it worked as expected, let me know whether this is also a corerct approach

AlB
Super User
Super User

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
Not applicable

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.

@Anonymous

 

What condition exactly?

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

 

Anonymous
Not applicable

@AlB - Had one question with your measure mentioned the total comes to be 125 which seems to be weird?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.