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

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,

 

 

 

v-yuta-msft
Community Support
Community Support

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

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

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 

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

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

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

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