Cost performance Index calculation

[Anonymous]

Hello,

I am new to power BI pls bear with me. I am trying to calculate Cost performance Index of a project within a fiscal year. Some of the project have a value of "0" for a specific month and some are just "blank". What I want to accomplish:

1. I want to be able to filter the blank and the "0".

2. The formula of the CPI is:

("Blank" month/"0" month) * (Monthly savings/YTD savings)

3. Based on the calculation above, I want to show:

CPI <1 over budget
       >1 under budget

       =1 on budget

Thanks for checking this out and I appreciate any help.

[VishalJhaveri]

@Anonymous Did my answer help you to resolve your query?

[Anonymous]

@VishalJhaveri Thanks for the quick reply! I will try this out and will let you know. Thanks again 🙂

[VishalJhaveri · Accepted solution]

Hi @Anonymous 
Please try this following measure and let me know if you're able to achieve the required scenario.

Test Measure =
VAR BlankValue = CALCULATE(COUNT(AnyColumn), Value = BLANK())
VAR Zeromonth = CALCULATE(COUNT(AnyColumn), Value = 0)
VAR CPI =
DIVIDE(BlankValue, ZeroMonth, 0) * DIVIDE(Monthly Savings, YTD Savings, 0)
RETURN
SWITCH(TRUE(),
CPI<1, "Over Budget",
CPI>1, "Under Budget",
CPI=1, "On Budget"
)

I hope it works.
Thank you.