cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## Total Not Matching with the Actual Numbers in the table

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:

I would appreciate any help! Thanks!

1 ACCEPTED SOLUTION
Community Support

@Greg_Deckler Thanks for your contribution on this thread.

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 REPLIES 8
Community Support

@Greg_Deckler Thanks for your contribution on this thread.

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper IV

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

I totally appreciate your help!

Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper IV

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!

Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper IV

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.

Helper IV

Great! Thanks for your help!

Super User

@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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements