Calculated Measures

[swathrddy]

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

[swathrddy]

Thank you Shravan, yes i am trying diffrent ways to achive different charts for my project. 

[Shravan133]

Please mark my answer as solution if i've answered your query.

[Shravan133 · Accepted solution]

However, here a few examples based on your data :

1. Total Actual Value Measure:

TotalActual = SUM('KPIData'[Actual])

1. Total Target Value Measure:

TotalTarget = SUM('KPIData'[Target])

Measures for "Number" Target Type

1. Number KPI Actual:

NumberActual =

CALCULATE(

    [TotalActual],

    'KPIData'[Target Type] = "Number"

)

1. Number KPI Target:

NumberTarget =

CALCULATE(

    [TotalTarget],

    'KPIData'[Target Type] = "Number"

)

1. Number KPI Achievement Percentage:

NumberAchievementPercentage =

DIVIDE([NumberActual], [NumberTarget])

Measures for "Percentage" Target Type

1. Percentage KPI Actual:

PercentageActual =

CALCULATE(

    [TotalActual],

    'KPIData'[Target Type] = "Percentage"

)

1. Percentage KPI Target:

PercentageTarget =

CALCULATE(

    [TotalTarget],

    'KPIData'[Target Type] = "Percentage"

)

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

)

1. Dynamic Actual Value:

KPI_ActualValue =

SWITCH(

    TRUE(),

    MAX('KPIData'[Target Type]) = "Number", [NumberActual],

    MAX('KPIData'[Target Type]) = "Percentage", [PercentageActual]

)

1. Dynamic Target Value:

KPI_TargetValue =

SWITCH(

    TRUE(),

    MAX('KPIData'[Target Type]) = "Number", [NumberTarget],

    MAX('KPIData'[Target Type]) = "Percentage", [PercentageTarget]

)

[Shravan133]

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