- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# subtotals are less than sum of column values

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-18-2018 08:26 AM

I am trying to count the number of unique cases by category and month. I am able to get the correct case counts within the matrix, but the column and row subtotals are incorrect. They are always less than the actual total of the entrys within the matrix. The image below is a snippet of the matrix. As you can see the columns do not add up correctly. The data is simply a list of cases with a date for the case, an activity code and date for the case. I am using the following formula to get the values within the matrix. It appears that the totals are doing a distinct count regardless of the date. As an example, the AA column has the same case number in both Oct and Nov. Is there a way to have the subtotals count the number of cases distinct to each row(i.e. date) so the sum is 2 and not 1?

Total SD Activities = CALCULATE(DISTINCTCOUNT(WprActivities_SDWithNoSD[CaseNumber]))

Solved! Go to Solution.

Accepted Solutions

## Re: subtotals are less than sum of column values

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-23-2018 12:45 AM

SUMX function will do the trick for you.

Im showing a working example on how you can use SUMX:

Metric = SUMX ( 'Calculated_Measures', CALCULATE (SUM('Master Table - All Services'[Cost_no_MT]) / MAX('Invoices to Clients'[TurnOver])) )

So in your case, make sure before the CALCULATE fucntion to put **SUMX**, then reference the relevant table and the put the calculation.

All Replies

## Re: subtotals are less than sum of column values

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-18-2018 08:42 PM

@tnohelty,

Create a new measure using DAX below and drag it to your visual , then check if the total value is correct.

NewMeasure = IF(COUNTROWS(VALUES(Table[YearMonth]))=1, [Total SD Activities],SUMX(VALUES(Table[YearMonth]),[Total SD Activities]))

Regards,

Lydia

## Re: subtotals are less than sum of column values

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-19-2018 06:12 AM

This solved the column total issue but the row totals are still wrong. This appears to be summing the Total SD Activities measure for each cell. I assume the theory is that when it hits the subtotal then it would sum each month because the context changes because of the values statement. Is there another trick to fix the total of each row. Seems like this trick works for one, but getting it to do both totals correctly will require multiple measures one for the dates and one for the activity codes.

## Re: subtotals are less than sum of column values

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-23-2018 12:17 AM

@tnohelty,

Please share sample data of your original table so that I can test.

Regards,

Lydia

## Re: subtotals are less than sum of column values

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-23-2018 12:45 AM

SUMX function will do the trick for you.

Im showing a working example on how you can use SUMX:

Metric = SUMX ( 'Calculated_Measures', CALCULATE (SUM('Master Table - All Services'[Cost_no_MT]) / MAX('Invoices to Clients'[TurnOver])) )

So in your case, make sure before the CALCULATE fucntion to put **SUMX**, then reference the relevant table and the put the calculation.

## Re: subtotals are less than sum of column values

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-25-2018 06:23 AM

The data is very simple. THere are three columns of data: Date, Case Number, Activity Code. THe columns of data look like the following:

Date Case # ActivityCode

1/2/2018 12345 AA

2/2/2018 12345 AA

3/2/2018 23456 AA

1/9/2018 12345 AM

4/9/2018 23456 AM

4/8/2018 23456 AA

I believe this has something to do with me using a distinct count of activity codes versus being able to use a SUMX. There seems to be something in the matrix that doesnt subtotal measures using distinct count formulas