Adding a column with the required logic

[Anonymous]

Hi All, I was hoping someone could help with this.

I have the following table:

Classification	Company	Value	(column needed)
E	Apple	10	60
E	Tesla	15	55
E	Apple	50	60
E	Tesla	40	55
S	Apple	20	30
S	Apple	10	30
S	Tesla	5	5

Basically I nede a column that does the sum of the value for each classification and company and repeats these values in the cells wherever the company name comes up.

For example Apple has to E's, valued 10 and 50. so I want my column to say 60 for both these fields.

Tried to use a mix of calculate, sum, and allexcept but not working. Please can someone help?

@amitchandak 

[CNENFRNL]

Hi, @Anonymous , I think you can add a calculated column like this

Calculated Column = SUMX (
    FILTER (
        ALL ( DS ),
        DS[Company] = EARLIER ( DS[Company] )
            && DS[Classification] = EARLIER ( DS[Classification] )
    ),
    DS[Value]
)

[ryan_mayu · Accepted solution]

@Anonymous 

please try

Column = CALCULATE(sum(Table1[Value]),ALLEXCEPT(Table1,Table1[Classification],Table1[Company]))

 

[Anonymous]

any idea how I can do this in powerquery so I can unpivot the data?

[Anonymous]

Thanks for this! works well. How can I amend it so that the score is averaged? i.e. because Apple has 2 'E' entries, I want to divide the answer from your code by this?

[ryan_mayu]

@Anonymous 

you can try this

Column = CALCULATE(sum(Table1[Value]),ALLEXCEPT(Table1,Table1[Classification],Table1[Company]))/CALCULATE(COUNTROWS(Table1),ALLEXCEPT(Table1,Table1[Classification],Table1[Company]))

why do you want to unpivot table? what's the output that you want?