Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
@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.
@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.
@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
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.
@Anonymous,
Please share sample data of your original table so that I can test.
Regards,
Lydia
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |