Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
RobRayborn
Helper III
Helper III

Incorrect totals with LASTNONBLANK.

‎07-14-2024 02:00 PM

I have files I receive monthly that I use to update shipping requirments month after month. 
Each file has a list of products as well as the ship month and the quantity that is to ship in those months.  I add a 'Date Create' to know when I received the file.
I use the SUMX function to sum all the data, then use the CALCULATE function to get the last non-blank value. 
When I add the 'Date Created to the Rows along with the Product, each row total is correct, however the grand total is not. 
I'm attaching some sample data in a .pbix file that will show what I am seeing. 
I need to show the Grand Total with corect quantitys when it is presented.
Please help me with this function to get the data I need.  I've reviewed multiple YouTube videos concerning "Incorrect Totals", and I have not been able to figure this out.  
ChatGPT is stumped as well.

Labels:
Message 1 of 8
687 Views
0
1 ACCEPTED SOLUTION
v-xuxinyi-msft
Community Support
Community Support
In response to RobRayborn

‎08-02-2024 02:55 AM

Hi @RobRayborn 

 

Please refer to the following test:

 

Create measures as follow

 

Measure2 =
IF (
    ISFILTERED ( 'Table'[Date Created] ),
    [YourUsingLASTNONBLANKMeasureName],
    IF (
        ISFILTERED ( 'Table'[Product] ),
        SUMX ( VALUES ( 'Table'[Date Created] ), [YourUsingLASTNONBLANKMeasureName] ),
        SUMX ( VALUES ( 'Table'[Product] ), [YourUsingLASTNONBLANKMeasureName] )
    )
)
Measure3 = 
IF(
    ISFILTERED('Table'[Ship Date]), [Measure2], SUMX(VALUES('Table'[Ship Date]), [Measure2])
    )

 

 

Output:

vxuxinyimsft_1-1722592292297.png

 

Best Regards,
Yulia Xu

 

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

Incorrect totals with LASTNONBLANK.pbix
Message 8 of 8
351 Views
0
7 REPLIES 7
RobRayborn
Helper III
Helper III

‎07-23-2024 12:20 PM

 Did not work.

RobRayborn_0-1721762436513.png

 

Message 7 of 8
452 Views
0
v-xuxinyi-msft
Community Support
Community Support
In response to RobRayborn

‎08-02-2024 02:55 AM

Hi @RobRayborn 

 

Please refer to the following test:

 

Create measures as follow

 

Measure2 =
IF (
    ISFILTERED ( 'Table'[Date Created] ),
    [YourUsingLASTNONBLANKMeasureName],
    IF (
        ISFILTERED ( 'Table'[Product] ),
        SUMX ( VALUES ( 'Table'[Date Created] ), [YourUsingLASTNONBLANKMeasureName] ),
        SUMX ( VALUES ( 'Table'[Product] ), [YourUsingLASTNONBLANKMeasureName] )
    )
)
Measure3 = 
IF(
    ISFILTERED('Table'[Ship Date]), [Measure2], SUMX(VALUES('Table'[Ship Date]), [Measure2])
    )

 

 

Output:

vxuxinyimsft_1-1722592292297.png

 

Best Regards,
Yulia Xu

 

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

Incorrect totals with LASTNONBLANK.pbix
Message 8 of 8
352 Views
0
v-xuxinyi-msft
Community Support
Community Support

‎07-14-2024 07:39 PM

Hi @RobRayborn 

 

Could you please share some sample data and the expected results based on the sample data? So that I can help you better. How to provide sample data in the Power BI Forum - Microsoft Fabric Community Or show them as screenshots or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

 

Best Regards,
Yulia Xu

Message 2 of 8
635 Views
0
RobRayborn
Helper III
Helper III
In response to v-xuxinyi-msft

‎07-15-2024 03:56 AM

RobRayborn_0-1721040964347.png

 

Message 5 of 8
617 Views
0
v-xuxinyi-msft
Community Support
Community Support
In response to RobRayborn

‎07-17-2024 12:45 AM

Hi @RobRayborn 

 

Thanks for sharing the sample data, please try the following formula:

Measure = SUMX(VALUES('YourTableName'[Product]), [YourUsingLASTNONBLANKMeasureName])

 

If the problem persists then I may need to know how your formula is written so I can test it.

 

Best Regards,
Yulia Xu

 

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

Message 6 of 8
541 Views
0
RobRayborn
Helper III
Helper III
In response to v-xuxinyi-msft

‎07-15-2024 03:53 AM

Is there a way for me to send you or upload the sample .pbix file?

 

Message 4 of 8
620 Views
0
RobRayborn
Helper III
Helper III
In response to v-xuxinyi-msft

‎07-15-2024 03:51 AM

Date CreatedProductShip DateQty.

12/10/2023AAA1/1/2024230
12/10/2023AAA2/1/2024880
12/10/2023AAA3/1/2024699
12/10/2023AAA4/1/2024733
12/10/2023AAA5/1/2024254
12/10/2023AAA6/1/2024798
12/10/2023AAA7/1/2024271
12/10/2023AAA8/1/2024437
12/10/2023AAA9/1/2024532
12/10/2023AAA10/1/2024275
12/10/2023AAA11/1/2024324
12/10/2023AAA12/1/20241080
12/10/2023AAA1/1/2025317
12/10/2023AAA2/1/2025966
12/10/2023BBB1/1/20241088
12/10/2023BBB2/1/2024547
12/10/2023BBB3/1/202495
12/10/2023BBB4/1/2024668
12/10/2023BBB5/1/2024464
12/10/2023BBB6/1/2024136
12/10/2023BBB7/1/2024747
12/10/2023BBB8/1/2024517
12/10/2023BBB9/1/2024454
12/10/2023BBB10/1/2024995
12/10/2023BBB11/1/2024761
12/10/2023BBB12/1/2024684
12/10/2023BBB1/1/2025275
12/10/2023BBB2/1/2025394
12/10/2023CCC1/1/2024580
12/10/2023CCC2/1/2024755
12/10/2023CCC3/1/2024681
12/10/2023CCC4/1/2024150
12/10/2023CCC5/1/2024674
12/10/2023CCC6/1/2024589
12/10/2023CCC7/1/2024263
12/10/2023CCC8/1/2024953
12/10/2023CCC9/1/20241015
12/10/2023CCC10/1/20241043
12/10/2023CCC11/1/2024131
12/10/2023CCC12/1/202427
12/10/2023CCC1/1/2025361
12/10/2023CCC2/1/20251051
1/15/2024AAA2/1/202499
1/15/2024AAA3/1/2024772
1/15/2024AAA4/1/2024465
1/15/2024AAA5/1/2024133
1/15/2024AAA6/1/202497
1/15/2024AAA7/1/2024384
1/15/2024AAA8/1/2024982
1/15/2024AAA9/1/2024527
1/15/2024AAA10/1/2024268
1/15/2024AAA11/1/2024812
1/15/2024AAA12/1/202455
1/15/2024AAA1/1/2025189
1/15/2024AAA2/1/2025601
1/15/2024BBB2/1/202494
1/15/2024BBB3/1/2024757
1/15/2024BBB4/1/202440
1/15/2024BBB5/1/2024481
1/15/2024BBB6/1/2024248
1/15/2024BBB7/1/2024790
1/15/2024BBB8/1/20241033
1/15/2024BBB9/1/2024810
1/15/2024BBB10/1/2024346
1/15/2024BBB11/1/2024142
1/15/2024BBB12/1/2024362
1/15/2024BBB1/1/2025449
1/15/2024BBB2/1/202592
1/15/2024CCC3/1/202597
1/15/2024CCC2/1/2024637
1/15/2024CCC3/1/2024891
1/15/2024CCC4/1/2024696
1/15/2024CCC5/1/2024331
1/15/2024CCC6/1/2024741
1/15/2024CCC7/1/20241032
1/15/2024CCC8/1/20241015
1/15/2024CCC9/1/20241072
1/15/2024CCC10/1/2024152
1/15/2024CCC11/1/2024633
1/15/2024CCC12/1/2024549
1/15/2024CCC1/1/2025629
1/15/2024CCC2/1/2025604
1/15/2024CCC3/1/2025312
2/15/2024AAA3/1/2024151
2/15/2024AAA4/1/2024541
2/15/2024AAA5/1/2024414
2/15/2024AAA6/1/2024446
2/15/2024AAA7/1/2024546
2/15/2024AAA8/1/2024885
2/15/2024AAA9/1/2024108
2/15/2024AAA10/1/2024854
2/15/2024AAA11/1/20241091
2/15/2024AAA12/1/2024177
2/15/2024AAA1/1/2025778
2/15/2024AAA2/1/2025926
2/15/2024AAA3/1/2025289
2/15/2024AAA4/1/2025803
2/15/2024BBB3/1/2024917
2/15/2024BBB4/1/2024418
2/15/2024BBB5/1/2024489
2/15/2024BBB6/1/2024528
2/15/2024BBB7/1/2024925
2/15/2024BBB8/1/2024144
2/15/2024BBB9/1/20241011
2/15/2024BBB10/1/2024552
2/15/2024BBB11/1/2024663
2/15/2024BBB12/1/202479
2/15/2024BBB1/1/202510
2/15/2024BBB2/1/2025863
2/15/2024BBB3/1/2025909
2/15/2024BBB4/1/20251042
2/15/2024CCC3/1/2024294
2/15/2024CCC4/1/2024765
2/15/2024CCC5/1/2024758
2/15/2024CCC6/1/20241059
2/15/2024CCC7/1/20241063
2/15/2024CCC8/1/2024807
2/15/2024CCC9/1/2024195
2/15/2024CCC10/1/2024685
2/15/2024CCC11/1/2024251
2/15/2024CCC12/1/2024129
2/15/2024CCC1/1/20251024
2/15/2024CCC2/1/2025615
2/15/2024CCC3/1/202576
2/15/2024CCC4/1/2025773
2/15/2024CCC5/1/2025415
Message 3 of 8
583 Views
0

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

View All