cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
v-jacogs Occasional Visitor
Occasional Visitor

Help is there a function to have subtotals in a column?

The sample table:

GroupDateSales
ADec100
AJan100
AFeb100
AMar100
BJan100
BFeb100
BMar100

The slicer applied:

 skicer.PNG

I want the table to look like:

GroupDateSalesGroupTotal
AJan100200
AFeb100200
BJan100200
BFeb100200

the Group total representing the subtotal by group.

 

A = CALCULATE( SUM('Table'[Sales]),ALLSELECTED())   

GroupDateSalesA
AJan100400
AFeb100400
BJan100400
BFeb100400

this gets the total for everything that is not filterred out not a subtotal fro each group though.

 

B=CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[Date]))

 

GroupDateSalesB
AJan100400
AFeb100400
BJan100300
BFeb100300

this gets a subtotal for each group. This subtotal includes data that was filtered out.

 

How can I do both?

2 REPLIES 2
Community Support Team
Community Support Team

Re: Help is there a function to have subtotals in a column?

Hi @v-jacogs,

 

You could try to create the measure below to get your excepted output.

 

Group_total =
CALCULATE ( SUM ( test[Sales] ), ALLSELECTED ( test[Group] ) )

Then you will get the result.

 

Capture1.PNG

 

In addition, you could create the Matrix visual and get the Subtotal simply as below.

 

Capture.PNG

 

Hope it can help you!

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jigon Regular Visitor
Regular Visitor

Re: Help is there a function to have subtotals in a column?

I have the same problem, and your solution seems not working for me... 

Could you take a look and find why, please? 

 

Capture.PNG

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 45 members 1,048 guests
Please welcome our newest community members: