Total Not Matching with the Actual Numbers in the table

[jovendeluna21]

Hello,

Anyone who would know why the total is not matching with the expected total?

The expected Dollar Impact ($) total should be 108 only but it's giving me 2,186.

Then on the per month table, the values per month are not matching with the right total.

On the monthly table, this should be the expected breakdown I highlighted below:

Maybe there's a need to update my dax calculation, and this is my calculation reference

Attached on this link the pbi copy and excel file:

https://drive.google.com/file/d/1K7_1OwlhD_w70wkBHnmcSnTfDDJQ9-iq/view?usp=sharing

https://docs.google.com/spreadsheets/d/1p0nwvskWW05B_bePsTjvHmWD_bMkPu1J/edit?usp=sharing&ouid=11683...

I would appreciate any help! Thanks!

[v-yiruan-msft · Accepted solution]

@Greg_Deckler Thanks for your contribution on this thread.

Hi @jovendeluna21 ,

I updated your sample pbix file, please check if that is what you want. You can follow the steps below to get it:

1. Update the formula of following 2 measures as below:

Prev Total Qty = 
VAR CurrentYear =
    MAX ( 'Sample'[Fiscal Year Number] )
VAR PrevYear = CurrentYear - 'Last N'[Var]
RETURN
    CALCULATE (
        SUM ( 'Sample'[Converted Received Quantity - Base UOM] ),
        FILTER (
            ALL ( 'Sample' ),
            'Sample'[Site PN] = SELECTEDVALUE ( 'Sample'[Site PN] )
                && 'Sample'[Fiscal Year Number] = PrevYear
        )
    )

PrevYear Spend = 
VAR CurrentYear =
    MAX ( 'Sample'[Fiscal Year Number] )
VAR PrevYear = CurrentYear - 'Last N'[Var]
RETURN
    CALCULATE (
        SUM ( 'Sample'[Actual Amount (USD)] ),
        FILTER (
            ALL ( 'Sample' ),
            'Sample'[Site PN] = SELECTEDVALUE ( 'Sample'[Site PN] )
                && 'Sample'[Fiscal Year Number] = PrevYear
        )
    )

2. Create another new measure as below to replace the original measure [Dollar Impact ($)]

Dollar impart with correct total values = SUMX(VALUES('Sample'[Site PN]),[Dollar Impact ($)])

Best Regards

[jovendeluna21]

Thank you @v-yiruan-msft! You're such a big help! By the way, is there a way to update the '% Comparable Dollar Impact' to be 7.42%? The calculation is something like this (Dollar Impact/(Total USD Spend Amount-Dollar Impact)). Currently it's showing 100% for all months.

This is the goal

I upload here the pbi copy: https://drive.google.com/file/d/1pbH-6ZqsFbUFG9bATHTk_xe7ISB87NNF/view?usp=sharing

https://docs.google.com/spreadsheets/d/1p0nwvskWW05B_bePsTjvHmWD_bMkPu1J/edit?usp=sharing&ouid=11683...

I totally appreciate your help!

[v-yiruan-msft]

Hi @DryMouse555 ,

Please update the formula of measure [Comparable USD Spend 2] as below, please find the details in the attachment.

Comparable USD Spend 2 = 
VAR py =
    MAX ( 'Sample'[Fiscal Year Number] ) - 'Last N'[Var]
VAR _cyv = [Total USD Spend]
VAR a =
    SUMMARIZE ( 'Sample', [Site Id], [Internal Part Number], "cyv", _cyv )
VAR b =
    ADDCOLUMNS ( a, "pyv", CALCULATE ( _cyv, 'Sample'[Fiscal Year Number] = py ) )
RETURN
    SUMX ( FILTER ( b, NOT ISBLANK ( [pyv] ) ), [cyv] )

Best Regards

[jovendeluna21]

Thank you @v-yiruan-msft for your response ! I tried to add another part number in the filter and I just noticed that the total of [Comparable USD Spend 2] is not showing correctly. Instead of $18,841, it should be $9,421. I just need to report that metric as well and hopefully it's something can be fix. Thanks again for your help!

[v-yiruan-msft]

Hi @jovendeluna21 ,

Please create another new measure as below to replace this measure and put this new measure onto the visual:

00_Comparable USD Spend 2 = SUMX(VALUES('Sample'[Site PN]),[Comparable USD Spend 2])

By te way, you can also adopt the same method to handle it if the total value of measure is not correct. 

1. Create another new meaure as below

New Measure = SUMX(VALUES('Sample'[Site PN]),[measure with incorrect total value])​

2. Put the above new measure onto the visual to replace the original one

In addition, you can refer the following links to try to solve your problem...

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Dax for Power BI: Fixing Incorrect Measure Totals

Best Regards

[jovendeluna21]

Hi, @v-yiruan-msft . 

I have a 'Comparable' filter, and currently, I default the previous year calculation to -1. This means it defaults to 2023, but I want the comparable spend to be dynamic based on the selected fiscal year. For example, when I change to 2022, the current fiscal year number (which in my example is filtered to 2024) should be compared, meaning all the common Site PNs in 2022 and 2024 only. I tried to use 'Last N'[Var], but I am getting an error.

Can you help me with this? Thanks a lot.

https://drive.google.com/file/d/13mOcZwMqymf2cSguEtyEAHG74nnVwrpw/view?usp=sharing

[jovendeluna21]

Great! Thanks for your help!

[Greg_Deckler]

@jovendeluna21 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