How to get the sum of a measure column in a table

[Anonymous]

I am using the below formula to get the sum of hours from the most current work date (Current Earned-Used Hours +(-))...I see online that there are issues getting the total when using measures.  I want to create a card with the total hours versus using the totals at the end of a table which will have the wrong sum.  How can I write a formula for the card that will give me the proper sum of the Current Earned-Used Hours +(-)?

Current Earned-Used Hours +(-) =

CALCULATE(

    SUM('ProjectDetails'[Earned - Used Hours + (-)]),

    FILTER(

        ALL(ProjectDetails[Work Date]),

        'ProjectDetails'[Work Date]=MAX('ProjectDetails'[Work Date])

    )

)

[v-xicai]

Hi @Anonymous ,

You may change your original measure like DAX below.

Current Earned-Used Hours +(-) =
VAR d =
    CALCULATE (
        SUM ( 'ProjectDetails'[Earned - Used Hours + (-)] ),
        FILTER (
            ALL ( ProjectDetails[Work Date] ),
            'ProjectDetails'[Work Date] = MAX ( 'ProjectDetails'[Work Date] )
        )
    )
VAR _table =
    SUMMARIZE ( 'ProjectDetails', 'ProjectDetails'[Current Work Date], "_Value", d )
RETURN
    IF (
        HASONEVALUE ( 'ProjectDetails'[Current Work Date] ),
        d,
        SUMX ( _table, [_Value] )
    )

Best Regards,

Amy

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

[Anonymous]

I suspected that the solution lied somewhere with HASONEVALUE.  When entering the formula I got some errors as shown below.  Could it be related to the "Current Work Day" which is the same a the MAX (Work Date)?  My Current Work Day in my formula is MAX (Work Date).

Thoughts?  BTW...things for helping!

[v-xicai]

Hi @Anonymous ,

You may replace the 'ProjectDetails'[Current Work Date] with the first displaying field in your table visual.

Best Regards,

Amy

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

[Anonymous]

Brain cramp...what does "the first displaying field in your table visual" mean?

Thx for the quick response...I have to get this ironed out tonight so much appreciated!!!

[v-xicai]

Hi @Anonymous ,

Currently, the screenshot you showed is part of the table visual, right? You may find the first order field in the left of current table visual.

Best Regards,

Amy

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

[Anonymous]

This is the actual full table...so you are saying use the Project Number field since it is the first column?

[v-xicai]

Hi @Anonymous ,

Yes, you can try to replace the 'ProjectDetails'[Current Work Date] with 'ProjectDetails'[Project Number].

Best Regards,

Amy

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

[Anonymous]

 That worked with no error this time in the formula but the results are off.  Below is the formula as it sits now.  Below that you can see that the card total with that formula is -9,960 but the actual result should be -13,240.

What am I missing in the formula?  THX Again!

Current Earned-Used Hours +(-) TEST VERSION =

VAR d =

    CALCULATE (

        SUM ( 'ProjectDetails'[Earned - Used Hours + (-)] ),

        FILTER (

            ALL ( ProjectDetails[Work Date] ),

            'ProjectDetails'[Work Date] = MAX ( 'ProjectDetails'[Work Date] )

        )

    )

VAR _table =

    SUMMARIZE ( 'ProjectDetails', 'ProjectDetails'[Project Number], "_Value", d )

RETURN

    IF (

        HASONEVALUE ( 'ProjectDetails'[Project Number] ),

        d,

        SUMX ( _table, [_Value] )

    )

[v-xicai]

Hi @Anonymous ,

You may change the measure using ALLSELECTED('ProjectDetails') instead of ALL ( ProjectDetails[Work Date] ) like DAX below.

Current Earned-Used Hours +(-) TEST VERSION =
VAR d =
    CALCULATE (
        SUM ( 'ProjectDetails'[Earned - Used Hours + (-)] ),
        FILTER (
            ALLSELECTED('ProjectDetails'),
            'ProjectDetails'[Work Date] = MAX ( 'ProjectDetails'[Work Date] )
        )
    )
VAR _table =
    SUMMARIZE ( 'ProjectDetails', 'ProjectDetails'[Project Number], "_Value", d )
RETURN
    IF (
        HASONEVALUE ( 'ProjectDetails'[Project Number] ),
        d,
        SUMX ( _table, [_Value] )
    )

Best Regards,

Amy

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

[Anonymous]

Hi Amy...any other thoughts on how to tweak this formula?

Thx

Tim

[v-xicai]

Hi @Anonymous ,

Could you please share your sample data for further analysis, you can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Amy

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

[Anonymous]

Hi Amy,

Did you get a chance to look over the PBIX?

Thx

Tim

[v-xicai · Accepted solution]

Hi @Anonymous ,

You may delete the created measure [Current Earned-Used Hours +(-) TEST VERSION] in your pbix file, then create new measure which will refer to the original measure [Current Earned-Used Hours +(-)] using DAX below.

Current Earned-Used Hours +(-)_New =
VAR _table =
    SUMMARIZE (
        'ProjectDetails',
        'ProjectDetails'[Project Number],
        "_Value", [Current Earned-Used Hours +(-)]
    )
RETURN
    IF (
        HASONEVALUE ( 'ProjectDetails'[Project Number] ),
        [Current Earned-Used Hours +(-)],
        SUMX ( _table, [_Value] )
    )

Best Regards,

Amy

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

[Anonymous]

Amy...so a follow-up to my last posted question regarding Prior Earned-Used Hours.  So on a whim I modified your Current formula to try and get the totals for Prior Earned-Used Hours and it actually worked...to a point...it correctly gave me totals in instances where there were no filters for General Forman or if filtered by General Forman and there was only one record relating to that foreman.  On the two instances where there were two or more records with the General Foreman, the total for Prior Earned-Used Hours was BLANK.  Below is the formula as I modified it.  Any thoughts on what could cause the totals be blank in these instances?

Thx again!

MODIFIED FORMULA TO GET PRIOR EARNED-USED HOURS TOTALS:

Prior Earned-Used Hours +(-)_Card =
VAR _table =
SUMMARIZE (
'ProjectDetails',
'ProjectDetails'[Project Number],
"_Value", [Prior Earned-Used Hours +(-)]
)
RETURN
IF (
HASONEVALUE ( 'ProjectDetails'[Project Number] ),
[Prior Earned-Used Hours +(-)],
SUMX ( _table, [_Value] )
)

[Anonymous]

Wow that worked perfectly!!!!  Thx so much!   One last thing in the same vein.  The Prior Earned-Used Hours is a slightly different calculation but similar to the the Current Earned-Used Hours...it is included in the PBIX file I sent you.  How can I modify the below formula to get the same Total result as you got with the Current Hours?

Prior Earned-Used Hours +(-) =

VAR LatestDate = MAX('ProjectDetails'[Work Date])

VAR PrevDate = CALCULATE(MAX(ProjectDetails[Work Date]),'ProjectDetails'[Work Date]<LatestDate)

RETURN

CALCULATE(

    SUM(ProjectDetails[Earned - Used Hours + (-)]),

    ProjectDetails[Work Date]=PrevDate

)

[Anonymous]

[Anonymous]

Guess I am having one of those days...I grabbed the wrong files...use these links.  Thx again!

PBIX:

https://onedrive.live.com/embed?cid=20AC66F5ABA96652&resid=20AC66F5ABA96652%21131&authkey=AAnoTtLJpA... 

Database:

https://onedrive.live.com/embed?cid=20AC66F5ABA96652&resid=20AC66F5ABA96652%21132&authkey=ALgWnAU636...

This formula issues is in the card "CW Earned-Used Hrs".
Tim

[Anonymous]

Amy,

For some reason I do not see my response in the threads...just in case, here are the links again...hopefully they work!

PBIX:

https://onedrive.live.com/embed?cid=20AC66F5ABA96652&resid=20AC66F5ABA96652%21128&authkey=ANbW81wn8k... 
Database:

https://onedrive.live.com/embed?cid=20AC66F5ABA96652&resid=20AC66F5ABA96652%21127&authkey=AIHHHBNTuG... 

Thx for your kind assistance!

Tim

[Anonymous]

Thx...I will work on that tomorrow.

Much appreciated!

[Anonymous]

Weird...no change in results with the ALLSELECTED.