Forum Discussion

LasyaJ's avatar
LasyaJ
Frequent Visitor
7 years ago

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

 

8 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    7 years ago

    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's avatar
      LasyaJ
      Frequent Visitor
      7 years ago

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

       

      Thanks

      • v-yuta-msft's avatar
        v-yuta-msft
        Community Support
        7 years ago

        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 

  • rohit403's avatar
    rohit403
    Helper I
    6 years ago

    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,