Skip to main content
cancel
Showing results for 
Search instead 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

Reply
jovendeluna21
Helper IV
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.

 

jovendeluna21_0-1719259920945.png

 

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

jovendeluna21_1-1719260244449.png

 

 

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

 

jovendeluna21_2-1719260339232.png

 

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!

 

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

@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 ($)])

vyiruanmsft_0-1719306993905.png

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.

View solution in original post

8 REPLIES 8
v-yiruan-msft
Community Support
Community Support

@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 ($)])

vyiruanmsft_0-1719306993905.png

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.

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.

 

jovendeluna21_0-1719427071646.png

 

 

jovendeluna21_0-1719409249053.png

This is the goal

 

jovendeluna21_1-1719409289112.png

 

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!

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

 

vyiruanmsft_0-1719540122867.png

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.

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!

 

jovendeluna21_0-1719550669417.png

 

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

 

vyiruanmsft_0-1719554319816.png

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.

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.

 

jovendeluna21_0-1719565818277.png

jovendeluna21_1-1719565829021.png

 

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

 

 

Great! Thanks for your help!

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


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.