cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Impact of Change Calculation in Power BI

Hi All,

I am trying to calculate Impact of Change in Power BI.
I have to calculate impact of change in True Efficiency (TE) between two time periods ( years/months)  for different countries.
I have the logic in excel which is as follow.
True Efficiency Formula =  DIVIDE (SUM(Actual Production), SUM(Theoratical Production)
Actual Data
 Year Country Actual Production Theoretical Production True Efficiency 2023 A 6,481,576 7,622,021 85.0% 2023 B 13,159,455 14,755,804 89.2% 2023 C 75,400,705 90,262,543 83.5% 2022 A 8,429,013 10,227,331 82.4% 2022 B 16,825,630 19,544,658 86.1% 2022 C 69,063,113 83,938,837 82.3%

Adjustments in the Data for Impact of Change Calcuation.
1. We have made base/denominator (Theoratical Production) same for the True Efficiency calculation.
2. Impact of Change Calculation for 1 Country,
a) we are taking latest period Actual Production value in consideration for that country and for rest of the countries we consider previous period Actual Production to calculate True Efficiency.
b) After Calculating True Efficiency in above manner we are simply getting difference of Latest Period TE value and Previous Period Value,
We have below data.
c) we need to repeat this for all the countries.

 Year Country Actual Production Theoretical Production True Efficiency for Impact of Change Impact of Change 2023 A 8,697,066 10,227,331 83.2% 0.24% 2023 B 16,825,630 19,544,658 2023 C 69,063,113 83,938,837 2022 A 8,429,013 10,227,331 82.9% 2022 B 16,825,630 19,544,658 2022 C 69,063,113 83,938,837

I am not able to replicate step 2 of logic in Power BI.
Any help on this highly appreciated.

Descriptipn

5 REPLIES 5
Super User

Hi @srb803 from example it is not clear how your tables are organized, how your model look like so it is hard to propose solution. Share more details.

Proud to be a Super User!

Frequent Visitor

We have only one table having data in tabular form as shown above.
Columns:
Year | Period(Month) | Country | Actual Production | Theoratical Production
Calcaulation: TE% = Actual Production/Theoratical Production.
Impact of Change, explained above.

I understand this logic is little confusing.
Can we connect separately as per your availability to have more clarity ?

Super User

Hi @srb803 I inserted into Excel your data, as there are no many rows, and while checking with your data I noticed that your first overview is not the same as on your picture with comments, etc. first row so I do not get the TE amount same. This is confusing, which example data should I follow and how you get 83,2% from your picture with comments? this is 85% according to simple calculation?

Proud to be a Super User!

Frequent Visitor

Yes, second view picture is not same. As we have adjusted the numbers as per logic and calculations are different (Impact of Change Calculations) on image with comments.
TE New calculation =  sum( Actual Production for complete 2023 )/Sum(Theorarical production for complete 2023)

Super User

Hi @srb803

Please find following measures and outpu as on picture below (same % as yours)

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Act Prod 2022 =
VAR __year_selected="2022"--change year as wanted
RETURN
CALCULATE(
sum(Sheet1[Actual Production]),
Sheet1[Year]=__year_selected
)

Act Prod 2023 =
VAR __year_selected="2023"--change year as wanted
RETURN
CALCULATE(
sum(Sheet1[Actual Production]),
Sheet1[Year]=__year_selected
)
Theo Prod 2022 =
VAR __year_selected="2022"--change year as wanted
RETURN
CALCULATE(
sum(Sheet1[Theoretical Production]),
Sheet1[Year]=__year_selected
)

Theo Prod 2023 =
VAR __year_selected="2023"--change year as wanted
RETURN
CALCULATE(
sum(Sheet1[Theoretical Production]),
Sheet1[Year]=__year_selected
)

True Efficiency Formula 2022 =
VAR __actual_prod=[Act Prod 2022]
VAR __theo_prod=[Theo Prod 2022]
VAR __Result=DIVIDE(__actual_prod,__theo_prod)
RETURN __Result

True Efficiency Formula 2023 =
VAR __actual_prod=[Act Prod 2023]
VAR __theo_prod=[Theo Prod 2023]
VAR __Result=DIVIDE(__actual_prod,__theo_prod)
RETURN __Result

Impact of Change formula =
VAR __TE_formula_year_2023=[True Efficiency Formula 2023]
VAR __TE_formula_year_2022=[True Efficiency Formula 2022]
VAR __Result=__TE_formula_year_2023-__TE_formula_year_2022
RETURN
__Result

Proud to be a Super User!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors