Forum Discussion
Calculated Measures
Hi,
could you please create different measures based on KPI and Target Type for the below sample data: eventually to create charts/trends.
| KPI | Date | Actual | Target | Target Type |
| AAA | 1/01/2024 | 33 | 20 | Number |
| AAA | 1/02/2024 | 14 | 20 | Number |
| AAA | 1/03/2024 | 15 | 20 | Number |
| AAA | 1/04/2024 | 6 | 20 | Number |
| AAA | 1/05/2024 | 7 | 20 | Number |
| AAA | 1/06/2024 | 20 | 20 | Number |
| AAA | 1/07/2024 | 7 | 20 | Number |
| AAA | 1/08/2024 | 20 | 20 | Number |
| BBB | 1/01/2024 | 78% | 70% | Percentage |
| BBB | 1/02/2024 | 78% | 70% | Percentage |
| BBB | 1/03/2024 | 74% | 70% | Percentage |
| BBB | 1/04/2024 | 66% | 70% | Percentage |
| BBB | 1/05/2024 | 75% | 70% | Percentage |
| BBB | 1/06/2024 | 75% | 70% | Percentage |
| BBB | 1/07/2024 | 80% | 70% | Percentage |
| BBB | 1/08/2024 | 81% | 70% | Percentage |
However, here a few examples based on your data :
- Total Actual Value Measure:
TotalActual = SUM('KPIData'[Actual])
- Total Target Value Measure:
TotalTarget = SUM('KPIData'[Target])
Measures for "Number" Target Type
- Number KPI Actual:
NumberActual =
CALCULATE(
[TotalActual],
'KPIData'[Target Type] = "Number"
)
- Number KPI Target:
NumberTarget =
CALCULATE(
[TotalTarget],
'KPIData'[Target Type] = "Number"
)
- Number KPI Achievement Percentage:
NumberAchievementPercentage =
DIVIDE([NumberActual], [NumberTarget])
Measures for "Percentage" Target Type
- Percentage KPI Actual:
PercentageActual =
CALCULATE(
[TotalActual],
'KPIData'[Target Type] = "Percentage"
)
- Percentage KPI Target:
PercentageTarget =
CALCULATE(
[TotalTarget],
'KPIData'[Target Type] = "Percentage"
)
- Percentage KPI Achievement Percentage:
PercentageAchievementPercentage =
DIVIDE([PercentageActual], [PercentageTarget])
Dynamic Achievement Percentage:
KPI_AchievementPercentage =
SWITCH(
TRUE(),
MAX('KPIData'[Target Type]) = "Number", DIVIDE([NumberActual], [NumberTarget]),
MAX('KPIData'[Target Type]) = "Percentage", DIVIDE([PercentageActual], [PercentageTarget])
)
- Dynamic Actual Value:
KPI_ActualValue =
SWITCH(
TRUE(),
MAX('KPIData'[Target Type]) = "Number", [NumberActual],
MAX('KPIData'[Target Type]) = "Percentage", [PercentageActual]
)
- Dynamic Target Value:
KPI_TargetValue =
SWITCH(
TRUE(),
MAX('KPIData'[Target Type]) = "Number", [NumberTarget],
MAX('KPIData'[Target Type]) = "Percentage", [PercentageTarget]
)
4 Replies
Thank you Shravan, yes i am trying diffrent ways to achive different charts for my project.
Please mark my answer as solution if i've answered your query.
swathrddy If You are new to Power BI, please put some effort into learning to create mesaures. Ask ChatGpt or try creating measures on your own instead of asking the forum to do it for you. The forum can help if your measure doesnt work or if something is wrong.
However, here a few examples based on your data :
- Total Actual Value Measure:
TotalActual = SUM('KPIData'[Actual])
- Total Target Value Measure:
TotalTarget = SUM('KPIData'[Target])
Measures for "Number" Target Type
- Number KPI Actual:
NumberActual =
CALCULATE(
[TotalActual],
'KPIData'[Target Type] = "Number"
)
- Number KPI Target:
NumberTarget =
CALCULATE(
[TotalTarget],
'KPIData'[Target Type] = "Number"
)
- Number KPI Achievement Percentage:
NumberAchievementPercentage =
DIVIDE([NumberActual], [NumberTarget])
Measures for "Percentage" Target Type
- Percentage KPI Actual:
PercentageActual =
CALCULATE(
[TotalActual],
'KPIData'[Target Type] = "Percentage"
)
- Percentage KPI Target:
PercentageTarget =
CALCULATE(
[TotalTarget],
'KPIData'[Target Type] = "Percentage"
)
- Percentage KPI Achievement Percentage:
PercentageAchievementPercentage =
DIVIDE([PercentageActual], [PercentageTarget])
Dynamic Achievement Percentage:
KPI_AchievementPercentage =
SWITCH(
TRUE(),
MAX('KPIData'[Target Type]) = "Number", DIVIDE([NumberActual], [NumberTarget]),
MAX('KPIData'[Target Type]) = "Percentage", DIVIDE([PercentageActual], [PercentageTarget])
)
- Dynamic Actual Value:
KPI_ActualValue =
SWITCH(
TRUE(),
MAX('KPIData'[Target Type]) = "Number", [NumberActual],
MAX('KPIData'[Target Type]) = "Percentage", [PercentageActual]
)
- Dynamic Target Value:
KPI_TargetValue =
SWITCH(
TRUE(),
MAX('KPIData'[Target Type]) = "Number", [NumberTarget],
MAX('KPIData'[Target Type]) = "Percentage", [PercentageTarget]
)
