SUMX to total a measure

[jimbob2285]

Hi

I've created a measure in my Purchase Order (PO) table that sums the value of receipts from the receipt table:

• This is a meausre, so that it's reactive to a slicer on the page
• because it's a meausres, it doesn't show the correct total in the table visual
• which I've achieved with a second SUMX measure, but there must be a way to do this all with a single measure
• My two measure are:

ReceiptValue = 
CALCULATE(
    SUM(Receipt[Value]),
    FILTER(
        Receipt,
        Receipt[PONumber] = MAX(PO[PONumber]) && 
        [_SelectedMonth_CUM_R] = 1
    )
)

ReceiptValue_X = 
SUMX(
    VALUES(PO),
    PO[ReceiptValue]
)

• I've also attached the Example PBIX file

Could anyone help condense these two measures into a single measure please

Cheers

Jim

[v-huijiey-msft · Accepted solution]

Hi @jimbob2285 ,

Thank you for your interest in this case.

The reason for the error is that this measure is not calculated correctly, and since there is no relationship between the two tables, slicer does not affect this value, and MAX(Receipt[Date]) always returns 1/12/2024.

The solution is to create a Measure:

Measure =
VAR MyFilterTable =
    SUMMARIZE (
        FILTER (
            ALL ( 'CalendarFilter' ),
            'CalendarFilter'[Date] <= SELECTEDVALUE ( 'CalendarFilter'[EOMonth] )
        ),
        'CalendarFilter'[Date]
    )
VAR _vtable =
    SELECTCOLUMNS (
        'Receipt',
        'Receipt'[PONumber],
        "_Value", IF ( 'Receipt'[Date] IN MyFilterTable, 1 )
    )
RETURN
    IF (
        ISINSCOPE ( PO[PONumber] ),
        SUMX (
            FILTER ( _vtable, [PONumber] = SELECTEDVALUE ( PO[PONumber] ) ),
            [_Value]
        ),
        SUMX ( _vtable, [_Value] )
    )

The final page visualization is shown below:

The pbix file is attached for your reference.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[BIswajit_Das · Accepted solution]

Hello @jimbob2285 
As per your requirement you can check the below DAX

ReceiptValue =

SUMX(SUMMARIZE('PO','PO'[PONumber],"RESULT",

CALCULATE(

    SUM(Receipt[Value]),

    FILTER(

        Receipt,

        Receipt[PONumber] = MAX(PO[PONumber]) &&

        [_SelectedMonth_CUM_R] = 1

    )

)

),[RESULT])

Thanks & Regards

[v-huijiey-msft · Accepted solution]

Hi @jimbob2285 ,

Thank you for your interest in this case.

The reason for the error is that this measure is not calculated correctly, and since there is no relationship between the two tables, slicer does not affect this value, and MAX(Receipt[Date]) always returns 1/12/2024.

The solution is to create a Measure:

Measure =
VAR MyFilterTable =
    SUMMARIZE (
        FILTER (
            ALL ( 'CalendarFilter' ),
            'CalendarFilter'[Date] <= SELECTEDVALUE ( 'CalendarFilter'[EOMonth] )
        ),
        'CalendarFilter'[Date]
    )
VAR _vtable =
    SELECTCOLUMNS (
        'Receipt',
        'Receipt'[PONumber],
        "_Value", IF ( 'Receipt'[Date] IN MyFilterTable, 1 )
    )
RETURN
    IF (
        ISINSCOPE ( PO[PONumber] ),
        SUMX (
            FILTER ( _vtable, [PONumber] = SELECTEDVALUE ( PO[PONumber] ) ),
            [_Value]
        ),
        SUMX ( _vtable, [_Value] )
    )

The final page visualization is shown below:

The pbix file is attached for your reference.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[jimbob2285]

Hi

I've applied new measures from both the code solutions that were kindly provided in:

• Example PBIX
• Both seem to be the same solution, it's just that one of them is wrapped in another Calculate
• However, both lose their total when I select an earlier month in the slicer
• My orignal secondary measure (ReceptValue_X) is the only one that retains the total when an earlier month is selected

What am I doing wrong?

Cheers

Jim

[Greg_Deckler]

@jimbob2285 Posting this to vote for the idea. First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[anmolmalviya05]

Hi , hope you are doing good!
You can consolidate the logic into a single measure to calculate the correct total directly in the table visual without needing a second measure. Here's the improved single-measure approach:

ReceiptValue =
SUMX(
VALUES(PO[PONumber]),
CALCULATE(
SUM(Receipt[Value]),
FILTER(
Receipt,
Receipt[PONumber] = MAX(PO[PONumber]) &&
[_SelectedMonth_CUM_R] = 1
)
)
)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

Let's Connect on LinkedIn: https://www.linkedin.com/in/anmol-malviya/?originalSubdomain=in

Subscribe my youtube channel for Microsoft Fabric and Power BI updates: https://www.youtube.com/@AnmolPowerBICorner

@jimbob2285

[jimbob2285]

Hi

Thanks, I've applied your code as a measure:

• In Example PBIX
• However, it loses the total when I select an earlier month in the slicer
• But my orignal secondary measure (ReceptValue_X) seems to retain the total 

What am I doing wrong?

Cheers

Jim

[bhanu_gautam]

@jimbob2285 , Use SUMX within calculate like

ReceiptValue_Combined =
CALCULATE(
SUMX(
VALUES(PO[PONumber]),
CALCULATE(
SUM(Receipt[Value]),
FILTER(
Receipt,
Receipt[PONumber] = MAX(PO[PONumber]) &&
[_SelectedMonth_CUM_R] = 1
)
)
)
)

[jimbob2285]

Hi

Thanks, I've applied your code as a measure:

• In Example PBIX
• Which seems to be the same solution as anmolmalviya05's but yours is wrapped in an additional calcuate
• However, it loses the total when I select an earlier month in the slicer
• But my orignal secondary measure (ReceptValue_X) seems to retain the total 

What am I doing wrong?

Cheers

Jim