Sum and Average on the same Column

[Anonymous]

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.

[Anonymous · Accepted solution]

see sample

https://ufile.io/zosqp

[Anonymous · Accepted solution]

see sample

https://ufile.io/zosqp

[Anonymous]

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 · Accepted solution]

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

[Anonymous]

Thank you very much once again its been very helpful !!!!

Something last. Is there any possibility to make this measure value?

What I am trying to do is to imput it on the Table sorter app. But is shows it as black which means that it does not read it.

what I am thinking is that it willl read it if it was value.

Thank you 🙂

[Anonymous]

To be honest I don't think theres a way to convert to value and keep the formatting as 2 separate types

[Anonymous]

Another solution which I thought is to create a different table. But I dont have the rank here. I could do it for the months with the below formula

Rank = RankX(ALLSELECTED('Date'[Month/Yeart]); Calculate(SUM((('data-Table'[Amount])))))

But for the Quarter it does not work! i tried to do that but it appears only 1

RankQ = RANKX(ALL('Date'[Month/Yeart].[Quarter]);CALCULATE(sum('Data-Table'[Amount])))

 

[kofi123]

i have a similar problem. i have a column that contains sum or average. i want to create a measure that will return sum or averages of contents in another column say target or actual.

KPI                 target    actual      calculation 

weight               60        40            sum

length                12        10            sum

%growth             3           1             average

[Anonymous]

what I am trying to fix is the below image! I would like to see a coma that separates the thousands so it can easable to read.

Any idea?

[Anonymous]

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