Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
srb803
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
YearCountryActual ProductionTheoretical ProductionTrue Efficiency
2023A              6,481,576                        7,622,02185.0%
2023B            13,159,455                     14,755,80489.2%
2023C            75,400,705                     90,262,54383.5%
2022A              8,429,013                     10,227,33182.4%
2022B            16,825,630                     19,544,65886.1%
2022C            69,063,113                     83,938,83782.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.

YearCountryActual ProductionTheoretical ProductionTrue Efficiency for Impact of ChangeImpact of Change
2023A8,697,06610,227,33183.2%0.24%
2023B16,825,63019,544,658  
2023C69,063,11383,938,837  
2022A8,429,01310,227,33182.9% 
2022B16,825,63019,544,658  
2022C69,063,11383,938,837  


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

Descriptipn
Logic Description.png

5 REPLIES 5
some_bih
Super User
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.





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

Proud to be a Super User!






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 ?

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?

 

some_bih_0-1692599665070.png

 

some_bih_1-1692599743784.png

 

 





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

Proud to be a Super User!






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)

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

some_bih_0-1692618636386.png

 





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

Proud to be a Super User!






Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.