Forum Discussion
Total as per category,Region,date
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 |
Your solutions is so great danextian , allow me to add some detailed steps.
Hi, AnonymousFirst, 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.
4 Replies
- danextian
Super User
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
Your solutions is so great danextian , allow me to add some detailed steps.
Hi, AnonymousFirst, 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.
