Forum Discussion
Cost performance Index calculation
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.
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.
3 Replies
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.