Impact of Change Calculation in Power BI

[srb803]

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

[some_bih]

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.

[srb803]

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 ?

[some_bih]

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?

[srb803]

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)

[some_bih]

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