Forum Discussion

Omega's avatar
Omega
Impactful Individual
9 years ago
Solved

Absolute at total

Dears,   I am currently trying to calculate the absolute difference between imports and exports at SKU level in the total. For the same SKU, I have imports and exports in every week. At the end of ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Omega,

    Create the following measures and check if you get expected result.

    NewMeasure = IF(COUNTROWS(VALUES(Table2[SKU]))=1,Table2[ABSDIFF],SUMX(VALUES(Table2[SKU]),[ABSDIFF]))
    Measure 3 = [NewMeasure]/[ExportTotal]



    Regards,
    Lydia

dedelman_clng's avatar
dedelman_clng
Community Champion
9 years ago

Can you share the code for your measure(s) ? PowerBI visuals don't total up columns and rows on measures - it calculates the measure based on the current filter context.

  • Omega's avatar
    Omega
    Impactful Individual
    9 years ago

    Measure = SUMX(Table2,ABS(Table2[Export]-Table2[Import]))

    • dedelman_clng's avatar
      dedelman_clng
      Community Champion
      9 years ago

      Your code is doing the absolute value, then summing.  You want to sum then do absolute value

       

      Measure = ABS(SUMX(Table2, (Table2[Export]-Table2[Import])))

      • Omega's avatar
        Omega
        Impactful Individual
        9 years ago

        Thanks!!! My bad didn't realize I'm doing it wrong. Now, I want to take the total absolute value (470) and divide it by the total export per SKU (8510). I tried the following measure: 

         

        Measure 2 = SUMX(Table2,[Measure])/SUMX(Table2,Table2[Export]) but I'm not getting correct % :(

         

        I even tried: Measure 2 = SUMX(Table2,[Measure])/SUM(Table2[Export]) but still wrong because it gets me the total export for all the SKUs :(

         

        Please advise. 

         

        Thanks!