Sum Total based on Columns

[punksterz626]

Hello All,

How can I achieve the outcome listed below for Total Capacity? I am using a matrix table and I want to add a column like Total Capacity below that shows the Job Cap total based on Employee. Is this possible? 

Employee	Job Cap	Total Capacity
A	25	100
A	25	100
A	25	100
A	25	100
B	40	80
B	40	80
C	20	60
C	20	60
C	20	60
D	10	50
D	10	50
D	10	50
D	10	50
D	10	50
F	30	30

[ddpl]

Hi Try this one

Column =
CALCULATE ( SUM ( 'Table'[Job Cap] ), DISTINCT ( 'Table'[Employee] ) )

==Mark solution as accepted if its worked

[punksterz626]

The result is the same as job cap and not the total.

[Ashish_Mathur]

Hi,

Write this calculated column formula

Total capacity = calculate(sum(Data[job cap]),filter(Data,Data[Employee]=earlier(Data[Employee])))

Hope this helps.

[punksterz626]

This didnt work for me. Getting an error EARLIER/EARLIEST refres to an earlier row context which doesnt exist. 

FYI - The rows might not necessarily be sequential by employees. They are not in specific orders if that helps.

[Ashish_Mathur]

Share the link from where i can download your PBI file.

[mlozano]

Try this:

Total Capacity = CALCULATE(
SUM(Tabla[límite de trabajo]), ALL(Tabla[Empleado]))

[punksterz626]

This did not work for me. The results are the same as the job cap.

[ddpl]

this is result you want right??

[punksterz626]

It is what i wanted but when i try to follow the same DAX it yields differently.

Total Capacity = CALCULATE(SUM(ProgressTable[Capacity Score]),DISTINCT(ProgressTable[Planner]))

[v-yalanwu-msft · Accepted solution]

Hi, @punksterz626 ;

In the image above,  can you share yours [Planner] column?Because this is a  [Planner] column grouping.

Base on the example you offered. You could try this dax.

Total capacity 2 = CALCULATE(SUM([Job Cap]),ALLEXCEPT('Table','Table'[Employee]))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[punksterz626]

This actually works! Thank you.