Forum Discussion
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% | 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?
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] )
)
7 Replies
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] )
)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.
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.
