Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors