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
Super 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
Helper 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
    Super User
    2 years ago

    Hi,

    Share the download link of the PBI file.

    • Gattinomio's avatar
      Gattinomio
      Helper I
      2 years ago

      Thank you very much.

      I found the solution by myself in the end.