subtotals are less than sum of column values

[Anonymous]

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

[themistoklis · Accepted solution]

@Anonymous

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.

[v-yuezhe-msft]

@Anonymous,

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

[Anonymous]

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.

[v-yuezhe-msft]

@Anonymous,

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

Regards,
Lydia

[Anonymous]

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