cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Credo Regular Visitor
Regular Visitor

Calculate subtotal using dax

I would like to calculate a subtotal using dax (not using the subtotal feature of matrix visual)

 

Sample data: 

 

ClientShopSales
A1100
A2200
B3400
B4500

 

 

Desired outcome: 

I want a measure that can calculate Client total for the displayed Shop without adding the client field itself to the visual

 

ShopClient Total
1300
2300
3900
4900

 

Best I could come up with was 

CALCULATE(SUM('Table1'[Sales]); ALL('Table1'[Shop]))

 

but it just gives me a repeating 1200 across all shops as it should unless I add client field to the table.

 

How can I add filter context for the client into the measure if its not part of a visual? 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Calculate subtotal using dax

@Credo Please try this as a "New Measure"

 

Test107 = CALCULATE(SUM(Test107Measure[Sales]),FILTER(ALL(Test107Measure),Test107Measure[Client]=SELECTEDVALUE(Test107Measure[Client]))) 

image.png



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

Proud to be a Datanaut !





View solution in original post

3 REPLIES 3
Super User
Super User

Re: Calculate subtotal using dax

@Credo Please try this as a "New Measure"

 

Test107 = CALCULATE(SUM(Test107Measure[Sales]),FILTER(ALL(Test107Measure),Test107Measure[Client]=SELECTEDVALUE(Test107Measure[Client]))) 

image.png



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

Proud to be a Datanaut !





View solution in original post

chirayuw Member
Member

Re: Calculate subtotal using dax

Add as measure

 

TotVal = CALCULATE(SUM(Table1[Sales]),ALLEXCEPT(Table1,Table1[Client]))

Credo Regular Visitor
Regular Visitor

Re: Calculate subtotal using dax

Thanks a lot

 

 

Helpful resources

Announcements
New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,016)