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.

     

stark1687's avatar
stark1687
Regular Visitor
8 years ago

Didnt quite work. 

 

My table has lots of different values for Event Type, Project and Vendor more like this. 

 

So for event CI the avg cost for vendor 3394 is 1,152,177.8, the avg for all vendors is 1,1518,13.97. So this vendor is pretty well aligned with the market. Whereas the avg for vendor 37608 is 2,396,125.21 which is way above. But I want to create a measure/column to do this analysis in the table that has lost of different vendors/events for each project. 

 

ProjectIDSum of ActualCostEventTypeVendor.1
MM003457                883,514.22CI3394
MM003458            1,273,172.80CI3394
MM003475            1,299,846.38CI3394
MM002604            2,396,125.21CI37608
MM003462            1,606,554.29CI38528
MM001967            3,694,483.24CI41371
MM002605            1,219,674.86CI41371
MM003442                776,005.65CI41371
MM004153                512,749.13CI41371
MM002632            2,035,097.14HGP3394
MM003384            2,293,902.88HGP3394
MM003390            6,977,007.28HGP3394
MM003412            2,410,819.81HGP3394
MM003429            6,320,683.18HGP3394
MM003441            5,393,296.40HGP3394
MM006737            2,708,156.58HGP3394
MM003433            5,649,589.79HGP38528
MM003492            1,426,222.69HGP38528
MM003482                745,867.94HGP41371
Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

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

 

  • stark1687's avatar
    stark1687
    Regular Visitor
    8 years ago

    That worked thanks 

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      You are welcome.

  • Gattinomio's avatar
    Gattinomio
    Helper 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
      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.