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

8 REPLIES 8
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

rohit403 Frequent Visitor
Frequent Visitor

New Query: Calculating weighted average using power bi dax

Hi,

I desperately need help in creating a dax expression.

 

First of all due to proprietary issue, I wont be able to share any data, but only a dummy data set.

 

I have a data set like this:

 

Company      KPI1    KPI2

A                    2%     3000

B                    1.8%   2500

C                     2%     4000

 

I need to calculate weighted average so that will get the result as

((KPI1 of A * KPI2 of A) + (KPI1 of B * KPI2 of B) + (KPI1 of C * KPI2 of C))/(KPI2 of A + KPI2 of B + KPI2 of C)

 

The challenge is the data is in different related tables and I would have to put a Slicer, whereby the same is calulated either for all the companies (A, B, C) or any one or more of them.

 

Any help from you guys would be be very helpful.

 

Thanks,

 

 

 

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 276 members 2,899 guests
Please welcome our newest community members: