Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Amy1010
Frequent Visitor

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 

 

RegionCategoryValueDate
AsiaContinous1002023-04
AsiaContinous2002023-05
AsiaContinous3002023-06
AsiaDiscrete9002023-04
AsiaDiscrete10002023-05
AsiaDiscrete10002023-06
AsiaSparge202023-04
AsiaSparge302023-05
APACDiscrete10002023-04
APACDiscrete20002023-05
APACDiscrete30002023-06
APACSparge502023-05
APACSparge1002023-06
APACContinous20002023-05
APACContinous30002023-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-

RegionCategoryValueDate
AsiaContinous1002023-04
AsiaContinous2002023-05
AsiaContinous3002023-06
AsiaDiscrete9002023-04
AsiaDiscrete10002023-05
AsiaDiscrete10002023-06
AsiaSparge202023-04
AsiaSparge302023-05
ASIATotal10202023-04
ASIATotal12302023-05
ASIATotal13002023-06
1 ACCEPTED SOLUTION
v-jianpeng-msft
Community Support
Community Support

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

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

1.Copy the original table

vjianpengmsft_0-1716866084747.png

vjianpengmsft_1-1716866142054.png

Select Group by:

vjianpengmsft_2-1716866180772.png

vjianpengmsft_3-1716866239517.png

vjianpengmsft_4-1716866262482.png

Click Close and Apply:

vjianpengmsft_5-1716866285756.png

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

vjianpengmsft_6-1716866363088.png

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:

vjianpengmsft_8-1716866710540.png

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.

View solution in original post

4 REPLIES 4
v-jianpeng-msft
Community Support
Community Support

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

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

1.Copy the original table

vjianpengmsft_0-1716866084747.png

vjianpengmsft_1-1716866142054.png

Select Group by:

vjianpengmsft_2-1716866180772.png

vjianpengmsft_3-1716866239517.png

vjianpengmsft_4-1716866262482.png

Click Close and Apply:

vjianpengmsft_5-1716866285756.png

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

vjianpengmsft_6-1716866363088.png

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:

vjianpengmsft_8-1716866710540.png

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.

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

danextian
Super User
Super User

Hi @Amy1010 ,

 

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

danextian_0-1716852625782.png

 










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


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Thank you for the solution 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors