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

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

Reply
bswank31
Helper II
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

SiteRigs
Program B subtotal2
A0
B0
K1
R1
Program G subtotal3
A0
C2
E0
U1
K0
Program M subtotal1
B1
K0
GrandTotal 5
1 ACCEPTED SOLUTION
v-kongfanf-msft
Community Support
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] ) )
    )
)

 

vkongfanfmsft_0-1709882903441.png

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.

View solution in original post

4 REPLIES 4
v-kongfanf-msft
Community Support
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] ) )
    )
)

 

vkongfanfmsft_0-1709882903441.png

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

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
Super User
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?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.