Percentage KPI for Expenditure and budget

[vtechBI]

Hello Colleages,

Please assist with DAX KPI calculation for the requirements below:

3/4 or 0.75 is given to calculate the KPI for the Third quarter of the year. (Can this value be hardcoded in powereBI to look it up during calculation)?

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

3 Third Quarter = 0.75 * Total appropriation
4 KPI = Sum of Expenditure Divided by Third Quarter

Please see attached sample pbix file for further assistance.

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

Thanks in advance.

[vtechBI]

Thanks so much this very helpful

[v-yilong-msft · Accepted solution]

Hi @vtechBI ,

Based on the pbix you provided, here are my answers.

First of all I think it's possible to create two calculated columns called Total Appropriation and Sum of Expenditures.Here are the DAX codes.

Total Appropriation = 
SUMX(
    FILTER(
        'KPI', 
        'KPI'[Budget Period] = "A23" &&
        'KPI'[Reporting Period] IN {"2023 Q1", "2023 Q2", "2023 Q3"}
    ), 
    'KPI'[Appropriation]
)

Sum of Expenditures = 
SUMX(
    FILTER(
        'KPI', 
        'KPI'[Budget Period] = "A23" &&
        'KPI'[Reporting Period] IN {"2023 Q1", "2023 Q2", "2023 Q3"}
    ), 
    'KPI'[Expenditure]
)

Then you can calculate the KPI.

KPI = 'KPI'[Sum of Expenditures] / (0.75*'KPI'[Total Appropriation])

In the end you'll be able to get the results you need.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly