cancel
Showing results for
Did you mean:
Frequent Visitor

## Measure total incorrect - multiple tables in sumx

Hi all,

I have a simple table. I am trying to find the absolute difference between two columns

For the measure column the total is not correct, it is showing the difference between the total of columns not the total of the measure

At the moment the formula for the measure is "Measure = abs(SUM(Sheet1[Qty]) - sum(CROSSTAB[M3]))"

I have looked through all the previous solutions to this problem and i came up with the below formula

"

Measure = if(isfiltered('Product Key'[Product Key - KPI]), abs(SUM(Sheet1[Qty]) - sum(CROSSTAB[M3])), sumx(......"

I am stuck with the sumx part of the formula. I do not know how to finish off the sumx part as column "m3" & "qty" are from different tables in the report

7 REPLIES 7
Community Support Team

## Re: Measure total incorrect - multiple tables in sumx

Hi

To be general, sumx or calculate function can be implemented on two different tables if they have relationship with each other like pattern below:

```Result =
SUMX ( FILTER ( Table1, Table1[Column1] = Condition1 ), Table2[Column2] )

Result =
CALCULATE (
SUM ( Table2[Column2] ),
FILTER ( Table1, Table1[Column1] = Condition1 )
)
```

Regards,

Jimmy Tao

Frequent Visitor

## Re: Measure total incorrect - multiple tables in sumx

Hi Jimmy,

sorry if this is a dumb question but what is Condition1?

Do I add "= Condition1" in my formula?

I changed it to the below but now I get a blank for my total

`Measure = if(isfiltered('Product Key'[Product Key - KPI]), abs(SUM(Sheet1[Qty]) - sum(CROSSTAB[M3])), CALCULATE(sum(CROSSTAB[M3]), FILTER(Sheet1,Sheet1[Qty])))`

Thanks

Community Support Team

## Re: Measure total incorrect - multiple tables in sumx

Hi LasyaJ,

`CALCULATE(sum(CROSSTAB[M3]), FILTER(Sheet1,Sheet1[Qty] >= 1)))`

Regards,

Jimmy Tao

Frequent Visitor

## Re: Measure total incorrect - multiple tables in sumx

Hi Jimmy,

Sorry I am a real newbie at this...

I am still getting a blank for my total in the measure column

`Measure = if(isfiltered('Product Key'[Product Key - KPI]), abs(SUM(Sheet1[Qty]) - sum(CROSSTAB[M3])), CALCULATE(sum(CROSSTAB[M3]), FILTER(Sheet1, Sheet1[Qty] >=1)))`

Could it be a problem with my data or is the formula I'm doing all wrong?

Thanks

Community Support Team

## Re: Measure total incorrect - multiple tables in sumx

Hi LasyaJ,

Have you created a relationship between 'Sheet1' and 'CROSSTAB', you should have relationship first before you call columns from other table.

Regards,

JImmy tao

Frequent Visitor

## Re: Measure total incorrect - multiple tables in sumx

Hi Jimmy,

There is no direct relationship between 'Sheet1' and 'CROSSTAB'

They are two different data sets. The only way they are linked is through a lookup table which has a common value

Community Support Team

## Re: Measure total incorrect - multiple tables in sumx

Hi LasyaJ ,

I think you mean "from different data source ", not "from different data set", right? So could you create relationship between 'Sheet1' and your lookuptable, ''CROSSTAB" and your lookuptable.

Regards,

Jimmy Tao

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 422 members 3,848 guests
Recent signins: