Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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] )
)
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.
Thanks a lot for clarification 🙂
@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.
This measure should return the expected results if 'Table' only contains one row per SKU.
Is it the case?
Yes.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |