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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
v-jacogs
New Member

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
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.