Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Sum and Average on the same Column

Dear All,

 

I need your help on this one. On my Excel file I have in one column amounts which are distinguished differently. I have Gross Profit and HeadCounts. The only thing that makes the difference is the description on the right column so for example:

 

Date              Amount  Type

01/01/2018    1000       Gross Profit

01/02/2018    2000       Gross Profit                         -->   Excel File

01/01/2018      10         Headcounts

01/02/2018      12         Headcounts

 

And the reason why I have all the amounts under the same columns is so I can create a table where I can have the dates on the columns and the types on the Rows.

 

Is there any way that I can choose for the Gross Profit to take the Sum and for the Headcounts the Average??

 

In case you need further details let me know about it.

 

Some like that is what I am trying to built but with the amounts under the years and now I am able to drill to quarters and months.

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago
    Measure = 
VAR GrossProfit =
FORMAT(SUM(Table1[Amount]),"#,##0")

VAR Headcounts =
FORMAT(AVERAGE(Table1[Amount]),"#,##0.0")

VAR Final =
SWITCH(SELECTEDVALUE(Table1[Type]),"Gross Profit", GrossProfit,"Headcounts", Headcounts)

Return
Final

9 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      It works perfectly! thank you very much !!!!!!!!

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hello, 

       

      An extra question came up here. On my Gross profit I want it without any decimals. But my Headcounts with one decimal.

       

      How can I do that?

       

      If I change it from the Format it changes for everthing.

       

      Thank you very much in advance!

       

      Cheers,

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago
        Measure = 
VAR GrossProfit =
FORMAT(SUM(Table1[Amount]),"#,##0")

VAR Headcounts =
FORMAT(AVERAGE(Table1[Amount]),"#,##0.0")

VAR Final =
SWITCH(SELECTEDVALUE(Table1[Type]),"Gross Profit", GrossProfit,"Headcounts", Headcounts)

Return
Final