Forum Discussion

stark1687's avatar
stark1687
Regular Visitor
8 years ago
Solved

Average by Column then Percent Difference

New PowerBI user here.    I have a set of data of project spend and I am trying to get the averages based upon two columns "Event" and "Vendor".  Then I want to compare actuals to %difference of...
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    You may download my solution file from here.  Hope this helps.

     

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
8 years ago

Hi,

 

You may download my solution file from here.  Hope this helps.

 

Gattinomio's avatar
Gattinomio
Icon for Helper I rankHelper I
2 years ago

Hi.

This is exactly what I am looking for but I am unable to download the file.
Is it still available?

Thanks a lot in advance.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    I do not have the file.  Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

    • Gattinomio's avatar
      Gattinomio
      Icon for Helper I rankHelper I
      2 years ago

      Hi,
      thanks a lot for your answer.

      I am trying to calculate the average of the values within a specific category and to then calculate the percentage change from the average for each of these values .

      Fruits CategoryFruitsVolumeCurrent Average Calculation Desired Average CalculationPercentage Change
      BerriesBlueberry31311,212-97.44%
       Boysenberry3293291,212-72.86%
       Cranberry65651,212-94.64%
       Currants7,2977,2971,212501.92%
       Gooseberry66661,212-94.56%
       Loganberry2252251,212-81.44%
       Raspberry4734731,212-60.98%
       Total8,4861,2121,212 

       

      The "Current Average Calculation" in blue is what I am currently getting with the following formula:

      # Total Average by Fruits =
      AVERAGEX(
              KEEPFILTERS(VALUES(Products[Fruits])),
              CALCULATE([Volume]))

      The total is correct but the problem is that I would like to have this total displayed on each row (Desired Average Calculation) instead to then be able to calculate the percentage change. Exactly what you showed in your screenshot above.

       

      As a side note, the fruits category is selected in the filter panel and it applies for the whole page but there are also some slicers on the dashboard. I would like these calculations to adjust accordingly to the filters selected in the slicers.

       

      Many thanks in advance for your input.

      Any suggestion would be really appreciated.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        2 years ago

        Hi,

        Share the download link of the PBI file.