Calculate Percentage KPI for Expenditure Against Appropriation

[vtechBI]

Dear Colleages,

I will like to calculate the percentage KPI as follows:

% KPI is calculated as  = Total of expenditures Against Total appropriation of a reporting period.

Sum of Expenditures(where[ Budget Period] column = (A23) and ([Reporting Period] column = ('2023 Q1','2023 Q2',''2023 Q3,'')

Against the Total Appropriation (where[ Budget Period] column = (A23) and ([Reporting Period] column = ('2023 Q1','2023 Q2',''2023 Q3,'') 

Please see attached sample pbix file for further assistance.

https://drive.google.com/file/d/1bgZI9YVLx478oJSUYo7U6aQdJLlx8dmk/view?usp=sharing 

Thanks in advance.

[bolfri · Accepted solution]

Hi,

I am not sure if it's what you're looking for, but...

Sum of Expenditures = 
CALCULATE(
    SUM('KPI'[Expenditure]),
    'KPI'[Budget Period] = "A23",
    'KPI'[Reporting Period] in {"2023 Q1","2023 Q2","2023 Q3"}
)

Total Appropriation = 
CALCULATE(
    SUM('KPI'[Appropriation]),
    'KPI'[Budget Period] = "A23",
    'KPI'[Reporting Period] in {"2023 Q1","2023 Q2","2023 Q3"}
)

Your KPI = DIVIDE([Sum of Expenditures],[Total Appropriation])

Let me know.

P.S. You should avoid to hardcore period filtering in measures. There are many wayes to do that better.

[vtechBI]

Please calculate the KPI as follows:

1 .Total appropriation =

CALCULATE(

    SUM('FIExpenditure'[Appropriation]),

    'FIExpenditure'[Budget Period] = "A23",

    'FIExpenditure'[Reporting Period] in {"2023 Q1","2023 Q2","2023 Q3","2023 Q4"})

2. Appropriation for 2023 Q3 =  3/4 of Total appropriation

3. KPI = Total Expenditure Divided by Appropriation for 2023 Q3

Thanks in advance.