Total as per category,Region,date

[Anonymous]

We need to calculate the total as per Category,Region and Date column and i need to add the same back as calulated column or measure back in the same dataset .How can i acheive it the Data set is like below format-

This data is further used further to create measures for diaplying in the line chart.and we have slicer named 

Region	Category	Value	Date
Asia	Continous	100	2023-04
Asia	Continous	200	2023-05
Asia	Continous	300	2023-06
Asia	Discrete	900	2023-04
Asia	Discrete	1000	2023-05
Asia	Discrete	1000	2023-06
Asia	Sparge	20	2023-04
Asia	Sparge	30	2023-05
APAC	Discrete	1000	2023-04
APAC	Discrete	2000	2023-05
APAC	Discrete	3000	2023-06
APAC	Sparge	50	2023-05
APAC	Sparge	100	2023-06
APAC	Continous	2000	2023-05
APAC	Continous	3000	2023-06

Total should get appended in the dataset with the sum of values for Region-ASIA  and sum of all the categories-Continous,Discrete,cyanide

Desired output-

Region	Category	Value	Date
Asia	Continous	100	2023-04
Asia	Continous	200	2023-05
Asia	Continous	300	2023-06
Asia	Discrete	900	2023-04
Asia	Discrete	1000	2023-05
Asia	Discrete	1000	2023-06
Asia	Sparge	20	2023-04
Asia	Sparge	30	2023-05
ASIA	Total	1020	2023-04
ASIA	Total	1230	2023-05
ASIA	Total	1300	2023-06

[v-jianpeng-msft · Accepted solution]

Your solutions is so great @danextian , allow me to add some detailed steps.
Hi, @Anonymous 

First, you need to group your raw dataset in Power Query by following these steps:

1.Copy the original table

Select Group by:

Click Close and Apply:

Click on the new table and enter the following DAX expression:

Table 2 = 
VAR _table1 = SUMMARIZE('Table (2)','Table (2)'[Region],'Table (2)'[Total],'Table (2)'[Date],"category","Total"
)
RETURN UNION('Table',SUMMARIZE(_table1,'Table (2)'[Region],[category],'Table (2)'[Total],'Table (2)'[Date]))

Here are the results:

I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

 @v-jianpeng-msft - It worked perfectly..Thanks for the clear solution 🙂

[danextian]

Hi @Anonymous ,

In DAX you can,

CALCULATE (
    SUM ( tbl[value] ),
    ALLEXCEPT ( tbl, tbl[region], tbl[category], tbl[date] )
)

but if the summary needs to be appended to another query, use the Group by feature in M

[Anonymous]

Thank you for the solution