Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Power Bi not calculating percentage correctly

Hello,
I'm running into a strange result when attemting to calculate percentage difference between 2 numbers and would really appreciate any incite into the problem. 

Measure: 

% Change =
     VAR diff =
       CALCULATE(TableA[Measure_Total_Sum] - TableA[Measure_Total_Sum_Prior_Yr])
     RETURN
       DIVIDE(diff , TableA[Measure_Total_Sum_Prior_Yr])
Using the actual numbers from my table, the calculation ends up being: (1,439,699 - 1,514,506) / 1,514,506 * 100 
Power BI ends up with -29.61%.
However, when I do this same calculation on a calculator, the end result is -4.94%. 
Now, here's the odd part... If I add the columns in a visual table, Power BI gets the same result as my calculator. 
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    So, it turns out, I was able to resolve this by going in the direction of all the help on this thread. I not only applied the year as a filter on the page, but also applied the months as a filter on the page as well. By choosing not to include the month of Oct. which is not complete yet, the measure calculates the percentage correctly. Thank you all who contributed. 

7 Replies

  • HoangHugo's avatar
    HoangHugo
    Icon for Solution Specialist rankSolution Specialist
    3 years ago

    Hi, the calculation is right, may be error from TableA[Measure_Total_Sum_Prior_Yr]. Can you share detail DAX of measure, and time table you are using.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks for your reply. 

      There are 2 measures associated with this particular measure. They are:

      Total Transactions = Sum(TableA[Transactions])

      Total Transactions Prior Yr = 
        CALCULATE(TableA[Total Transactions], PARALLELPERIOD('Calendar'[Date],-12,MONTH))

       

      The Calendar Table is a standard Calendar table I use with all reports. 

      When I add the months to the visual table (mentioned above), the row totals are all correct. 

      Here's a screenshot of the actual report: 

      • HoangHugo's avatar
        HoangHugo
        Icon for Solution Specialist rankSolution Specialist
        3 years ago

        Hi, I understand you want to calculate YOY%, so need to calculate result of current Year to Date, try this one

         

        Measure result of YTD = CALCULATE([Total Transaction], DATEYTD('Calendar'[Date]))

        Measure result of Prior Year =CALCULATE(TableA[Total Transactions], PARALLELPERIOD('Calendar'[Date],-12,MONTH))

         

        then, use your orginal formula to calculate %difference 

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    So, it turns out, I was able to resolve this by going in the direction of all the help on this thread. I not only applied the year as a filter on the page, but also applied the months as a filter on the page as well. By choosing not to include the month of Oct. which is not complete yet, the measure calculates the percentage correctly. Thank you all who contributed.