Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Issue with Totals for a visual

Power BIExcelThe 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 for all the months of the measured dates).  The "Excel" formula its showing the "Grand Total" or another words the sum of all the counts in the above rows.  Is there a way to force the total for a measure to become a "Grand Total" instead of what ever method the visual comes up with for that Visual????

Need Help

5 Replies

  • Anonymous's avatar
    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's avatar
      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's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft 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,