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

     

     

EminUZUN's avatar
EminUZUN
Frequent Visitor
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 :)