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.

     

v-yuta-msft's avatar
v-yuta-msft
Community Support
8 years ago

Hi stark1687,

 

Create a calculate column and try DAX like this:

 

 

Percentage =
CALCULATE (
    DIVIDE ( table[cost] - AVERAGE ( table[cost] ), table[cost] ),
    FILTER ( table, table[event] = EARLIER ( table[event] ) )
)

 

 

Regards,

Jimmy Tao

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.