cancel
Showing results for
Did you mean:
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:

 Client Shop Sales A 1 100 A 2 200 B 3 400 B 4 500

Desired outcome:

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

 Shop Client Total 1 300 2 300 3 900 4 900

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

## 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]))) `

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

Proud to be a Datanaut !

3 REPLIES 3
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]))) `

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

Proud to be a Datanaut !

Member

## Re: Calculate subtotal using dax

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

Regular Visitor

## Re: Calculate subtotal using dax

Thanks a lot

Announcements

#### 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

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

#### Power Platform World Tour

Find out where you can attend!

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