Forum Discussion

Omega's avatar
Omega
Impactful Individual
9 years ago
Solved

Define aggregation at total

Hi, I am new to power BI and I need some help with aggregation. Assume I have the below table:   SKU| Export| Import| KPI1%| KPI2% 001 | 500    | 100     | 80%   | 80% 002 | 600    | 200     | 67...
Need Help
  • EminUZUN's avatar
    EminUZUN
    9 years ago

     

     Hello,

     

    You may use following formula:

    KPI1% =
    DIVIDE (
        SUM ( Table1[Export] ) - SUM ( Table1[Import] ),
        SUM ( Table1[Export] )
    )

     

    KPI2% =
    DIVIDE (
        SUMX ( Table1, ABS ( Table1[Export] - Table1[Import] ) ),
        SUM ( Table1[Export] )
    )

     

     

Omega's avatar
Omega
Impactful Individual
9 years ago

Thanks a lot for the response. Small question: What summarization I have to choose? As you know, total row appears once the values are summarized.

LaurentCouartou's avatar
LaurentCouartou
Solution Supplier
9 years ago

The measures suggested by EminUZUN already define the aggregation to be used.

 

You have to use these measures instead of the source columns. In fact, you may even remove the columns from your model.

  • Omega's avatar
    Omega
    Impactful Individual
    9 years ago

    Thanks a lot for clarification :)

    • Omega's avatar
      Omega
      Impactful Individual
      9 years ago

      LaurentCouartou and EminUZUN

       

      Can you please help me again? I am having issue with KPI2%. At SKU level, KPI2% and KPI1% are different although they should be the same but different at total level. Attached example and formula for KPI2%. Please advise. 

       

       

       

       

       

      • LaurentCouartou's avatar
        LaurentCouartou
        Solution Supplier
        9 years ago

        This measure should return the expected results if 'Table' only contains one row per SKU.

         

        Is it the case?