cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LasyaJ Frequent Visitor
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 

 

Thank you for your help

 

2018-10-03_15-36-35.png

7 REPLIES 7
Community Support Team
Community Support Team

Re: Measure total incorrect - multiple tables in sumx

Hi LasyaJ,

 

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

LasyaJ Frequent Visitor
Frequent Visitor

Re: Measure total incorrect - multiple tables in sumx

Hi Jimmy,

 

Thanks for the reply

 

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

2018-10-04_20-59-58.png

 

Thanks

Community Support Team
Community Support Team

Re: Measure total incorrect - multiple tables in sumx

Hi LasyaJ,

 

You should add some conditions based on your requirement such as:

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

Regards,

Jimmy Tao 

LasyaJ Frequent Visitor
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
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

LasyaJ Frequent Visitor
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
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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 422 members 3,848 guests
Please welcome our newest community members: