cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

## Grand Total not adding correctly

Hi. I have searched for an answer but I cannnot quite get anything to work. I have a column that is counting the number of rigs.

Sum of Rigs max per Date =
MAXX(
KEEPFILTERS(VALUES('Calendar Dates'[Date])),
CALCULATE(SUM('Drill'[Rigs])
))

It is giving me the correct number per site and the correct number for that subtotal but the grand total is not correct. Any help would be much appreciated.
Here is what its giving me:The Program lines are the subtotals of the below sites. The Grand total should be 6

 Site Rigs Program B subtotal 2 A 0 B 0 K 1 R 1 Program G subtotal 3 A 0 C 2 E 0 U 1 K 0 Program M subtotal 1 B 1 K 0 GrandTotal 5
1 ACCEPTED SOLUTION
Community Support

Hi @bswank31 ,

Try to modify your formula like below, it can get correct total result:

``````result1 =
CALCULATE (
MAXX (
KEEPFILTERS ( VALUES ( 'Calendar'[Date] ) ),
CALCULATE ( SUM ( 'Drill'[Rigs] ) )
)
)``````

Best Regards,

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

4 REPLIES 4
Community Support

Hi @bswank31 ,

Try to modify your formula like below, it can get correct total result:

``````result1 =
CALCULATE (
MAXX (
KEEPFILTERS ( VALUES ( 'Calendar'[Date] ) ),
CALCULATE ( SUM ( 'Drill'[Rigs] ) )
)
)``````

Best Regards,

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

Helper II

Thanks for looking at my issue. That formula was just what I ended with that gave me almost what I wanted. I want to be able to filter by a Year and it will then give me the max number of rigs for each site for that year selected. Then have the correct sub totals for the programs and the grand total.  I do have another measure for Rig count that gives me almost what I need but it doesn't calculate the Sub totals or Grand total correctly.

Rig Count = SUMX(FILTER('Drill', ('Drill'[Date])= MAX('Drill'[Date])), [Rigs])

Super User

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Super User

That formula seems a little off. What's the point of the MAXX ? You want to find the number of rigs for the maximum date in your filter context?

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors