Grand Total not adding correctly

[bswank31]

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

[v-kongfanf-msft · Accepted solution]

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,
Adamk Kong

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

[bswank31]

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

[lbendlin]

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.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[lbendlin]

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?