Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to 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
Thanks so much this very helpful
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |