cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
surfingjoe
Helper I
Helper I

Issue with Totals for a visual

Power BIPower BIExcelExcelThe 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????

5 REPLIES 5
Anonymous
Not applicable

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

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

Hi @surfingjoe,

 

@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,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I do have a sample but my permissions do not allow me to attach a file

Hi @surfingjoe ,

 

You can upload it to the cloud drive like OneDrive then share the download link here. Please mask the sensitive parts.

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!