Define aggregation at total

[Omega]

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%   | 67%

003 | 700    | 1000   | -43%  | 43%

004 | 800    | 400     | 50%   | 50%

*KPI1% = (Export-Import)/Export, KPI2% = abs((Export-Import)/Export)

Calculating Total row:

        - Export_Total= sum of export at SKU level = 2600, Import_Total = sum of import at SKU level = 1700

        - KPI1% = (Export_Total - Import_Total)/ Export_Total = (2600-1700)/2600 = 34.6%

        - KPI2% = (Sum (abs (Export- import at SKU level)))/ Export_Total = 1500/2600 = 58%

How to implement KPI1 and KPI2% at Total row?

[EminUZUN · Accepted solution]

 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]

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]

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]

Thanks a lot for clarification 🙂

[Omega]

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

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

Is it the case?

[Omega]

Yes.