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

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!

 

 

vanessafvg's avatar
vanessafvg
Community Champion
9 years ago

Measure 2 = divide(SUMX(Table2,[Measure]),SUMX(Table2,Table2[Export]))

  • Omega's avatar
    Omega
    Impactful Individual
    9 years ago

    Same values as I had :(

    • vanessafvg's avatar
      vanessafvg
      Community Champion
      9 years ago

      divide(SUMX(Table2,[Measure]),calculate(SUMX(Table2,Table2[Export]),sku = 8510))

      • Omega's avatar
        Omega
        Impactful Individual
        9 years ago

        What if I have multiple SKUs?

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Omega,

      I can get your expected result by creating the following measures. How do you create visual in your scenario?

      ABSDIFF = ABS(SUMX(Table2, (Table2[Export]-Table2[Import])))
      ExportTotal = SUM(Table2[Export])
      Measure 2 = DIVIDE(Table2[ABSDIFF],Table2[ExportTotal])



      Regards,
      Lydia Zhang

      • Omega's avatar
        Omega
        Impactful Individual
        9 years ago

        It's close but when you combine SKUs it won't give the correct value because PBI will calculate the difference at week level then sum up the difference at SKU level. My aim is to calculate the total exports and imports per SKU -> Apply ABSDIFF -> Sum ABSDIFF and the divide by total exports.