Grouped Sum

[sprakash1192]

I have a summarized table like this:

Year	ProdFacName	Department Name	Week	Total_WCM_EUs
2020	Tyler	WCM	29	563892.44
2020	Tyler	WCM	30	589533.76

I would like to create a new column that has a total sum grouped by the rows like this :

Year	ProdFacName	Department Name	Week	Total_WCM_EUs	Total
2020	Tyler	WCM	29	563892.44	1153426.2
2020	Tyler	WCM	30	589533.76	1153426.2

How can I do this without create a new summarized table?

[AllisonKennedy]

@sprakash1192
Hopefully one of the suggested formulas has helped you, but I'm just curios why you need the summarized table and have chosen to do this as a calculated table. Depending on the ultimate end goal, this could be done using MEASURES and matrix/table visual instead. If you're looking for some data model efficiencies, let us know your ultimate goal.

[mahoneypat]

You can add a calculated column to your calculated table with this expression.

Total =
CALCULATE (
SUM ( Table[Total_WCM_EUs] ),
ALLEXCEPT ( Table, Table[ProdFacName], Table[Year], Table[Department] )
)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[parry2k]

@sprakash1192 add a new measure like this:

Total = CALCUATE ( SUM ( Table[TOTAL_WCM} ), ALL() )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[sprakash1192]

But it takes away all groups. I should have made my question clearer, I want to have a grouped sum by the Key in my table, like this :

Year	ProdFacName	Department Name	Week	Key	Total_WCM_EUs	Total
2020	Tyler	WCM	29	29-30	563892.4	1153426
2020	Tyler	WCM	30	29-30	589533.8	1153426
2020	Tyler	WCM	31	31-32	410000	9410000
2020	Tyler	WCM	32	31-32	9000000	9410000

[v-kelly-msft · Accepted solution]

Hi  @sprakash1192 ,

Or you can use "earlier" function:

Total = SUMX(FILTER('Table','Table'[Key]=EARLIER('Table'[Key])),'Table'[Total_WCM_EUs])

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[parry2k]

@sprakash1192 try this

Total  = 
CALCULATE ( SUM ( Table[Total] ), ALLEXCEPT ( Table, Table[Key] ) )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡