Forum Discussion
Grouped Sum
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?
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,
KellyDid I answer your question? Mark my post as a solution!
6 Replies
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.⚡
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 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.⚡
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
- 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.
