Forum Discussion

Anonymous

Not applicable

7 years ago

Issue with Totals for a visual

The Power BI photo is simply a count patients per day and created a date grouping by month. Each Month the count is correct but the total is not a grand total (possibly the distinct count fo...

Anonymous

Not applicable

7 years ago

If you wrap your function in SUMX (Values...) it will force the grand total to the sum of the above.

Total Patients Per Day = 
SUMX ( 
    Values( Table3[Month & Year] ), 
    CALCULATE( SUM ( Table3[Patients Per Day] ) )
)

Anonymous
Not applicable
7 years ago
I don't understand why I'd use
Values( Table3[Month & Year] )
for the grouping of dates by month??

Would it help to show the my original forumla for the measure patients per day

Patients per day = (CALCULATE(DISTINCTCOUNT(Source[Patient full name]),Source[Date]))

its real simple patients can have multiple orders per day and all I want is a unique countof patients per day. The counts per day are correct but the visual, I believe is giving me a distinct count over the entire year as the total instead grand total.

I tried simply using Value for the above formula and that comes up with wrong numbers for some strange reason
- v-jiascu-msft
  Microsoft Employee
  7 years ago
  Hi Anonymous,
  
  Anonymous 's solution should work if you replace the SUM with DISTINCTCOUNT. The [Month & Year] is the column from your Excel file. It could be like below.
  
  Total Patients Per Day = SUMX ( VALUES ( Table3[Date(bins)] ), CALCULATE ( DISTINCTCOUNT ( Table3[Patients Per Day] ) ) )
  
  Because it provides the month context for every month before summing up.
  
  Can you share a sample if you still have issues?
  
  Best Regards,
  - Anonymous
    Not applicable
    7 years ago
    I do have a sample but my permissions do not allow me to attach a file