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 Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Request now

Reply
tomasjezek
Frequent Visitor

Totals in pivot table excluding certain rows under condition

‎05-21-2024 11:49 AM

Hello,

 

I would like to create a DAX measure which will calculate booking value as sum of ending backlog + sum of shipments - sum of beginning backlog, but only in case there will values of beginning and ending backlog in pivot table. In case there will be only beginning backlog value, then the booking will be 0 or blank. Total value of booking column will be calculated in the same logic - so in case the ending backlog of certain row will be missing, then the whole row will be calculated as 0.

 

See the printscreen with final expected result I need in Power BI visualisation:

tomasjezek_0-1716317039512.png

 

I enclose dataset below as well.

 

Thank you very much in advance.

 

Best regards,

 

Tomas

 

BeginningBacklogEndingBacklogFiscal_YearFiscal_Week_NumSourcePROD_LINE Coverage$ ITEM_NUMBERWAREHOUSECUSTOMER_NAMEFY-FW
5/4/20245/11/2024202419Shipments1759                            -  OPEMEAC52024-19
5/4/20245/11/2024202419Shipments1759                           47U1EMEAC42024-19
5/4/20245/11/2024202419Shipments1759                            -  TEMEAC32024-19
5/4/20245/11/2024202419Shipments1759                     1,907TEMEAC32024-19
5/4/20245/11/2024202419Shipments1759                         715SEMEAC32024-19
5/4/20245/11/2024202419Shipments1759                     1,192SEMEAC32024-19
5/4/20245/11/2024202419Shipments1759                     1,143OPEMEAC52024-19
5/4/20245/11/2024202419Beginning Backlog1759                     8,453U1EMEAC42024-19
5/4/20245/11/2024202419Shipments1759                            -  SEMEAC32024-19
5/4/20245/11/2024202419Shipments1759                     7,430OPEMEAC52024-19
5/4/20245/11/2024202419Shipments1759                     3,242OPEMEAC52024-19
5/4/20245/11/2024202419Shipments1759                         339U2EMEAC42024-19
5/4/20245/11/2024202419Shipments1759                         408U2EMEAC42024-19
5/4/20245/11/2024202419Ending Backlog1759                         417OPEMEAC52024-19
5/4/20245/11/2024202419Ending Backlog1759                     7,295TEMEAC32024-19
5/4/20245/11/2024202419Ending Backlog1759                     8,988OPEMEAC52024-19
5/4/20245/11/2024202419Ending Backlog1760                     1,928QAPACC22024-19
5/4/20245/11/2024202419Shipments1753                   17,697U3APACC42024-19
5/4/20245/11/2024202419Shipments1753                         332U3APACC42024-19
5/4/20245/11/2024202419Shipments1753                         415OPAPACC52024-19
5/4/20245/11/2024202419Shipments1753                   27,802QAPACC22024-19
5/4/20245/11/2024202419Shipments1753                            -  OPKOREAC52024-19
5/4/20245/11/2024202419Shipments1753                   16,823U1APACC42024-19
5/4/20245/11/2024202419Beginning Backlog1752                         497OPEUC52024-19
5/4/20245/11/2024202419Beginning Backlog1752                         297OPEUC52024-19
5/4/20245/11/2024202419Beginning Backlog1752                         276AEUC12024-19
5/4/20245/11/2024202419Beginning Backlog1760                     7,815QKOREAC22024-19
5/4/20245/11/2024202419Beginning Backlog1759                     1,790QKOREAC22024-19
5/4/20245/11/2024202419Beginning Backlog1759                     6,587EEKOREAC22024-19
5/4/20245/11/2024202419Beginning Backlog1753                     8,881EEKOREAC22024-19
5/4/20245/11/2024202419Shipments1753                   15,431EEKOREAC22024-19
5/11/20245/18/2024202420Beginning Backlog1752                     2,017OPEUC52024-20
5/11/20245/18/2024202420Beginning Backlog1752                         380TEUC32024-20
5/11/20245/18/2024202420Beginning Backlog1752                     1,190TEUC32024-20
5/11/20245/18/2024202420Beginning Backlog1752                         669TEUC32024-20
5/11/20245/18/2024202420Beginning Backlog1752                         551SEUC32024-20
5/11/20245/18/2024202420Beginning Backlog1752                         950AEUC12024-20
5/11/20245/18/2024202420Beginning Backlog1752                           69BEUC12024-20
5/11/20245/18/2024202420Beginning Backlog1752                         159SEUC32024-20
5/11/20245/18/2024202420Beginning Backlog1752                             8SEUC32024-20
5/11/20245/18/2024202420Beginning Backlog1752                     1,078SEUC32024-20
5/11/20245/18/2024202420Ending Backlog1752                         950AEUC12024-20
5/11/20245/18/2024202420Ending Backlog1752                           69AEUC12024-20
5/11/20245/18/2024202420Ending Backlog1752                         380TEUC32024-20
5/11/20245/18/2024202420Ending Backlog1752                         159TEUC32024-20
5/11/20245/18/2024202420Ending Backlog1752                         669TEUC32024-20
5/11/20245/18/2024202420Ending Backlog1752                             8SEUC32024-20
5/11/20245/18/2024202420Ending Backlog1752                     1,078SEUC32024-20
5/11/20245/18/2024202420Ending Backlog1752                     1,190SEUC32024-20
5/11/20245/18/2024202420Ending Backlog1752                         551SEUC32024-20
5/11/20245/18/2024202420Ending Backlog1752                     1,848EEEUC22024-20
5/11/20245/18/2024202420Shipments1752                     1,319AEUC12024-20
5/11/20245/18/2024202420Ending Backlog1752                         430SEUC32024-20
5/11/20245/18/2024202420Ending Backlog1752                           12SEUC32024-20
5/11/20245/18/2024202420Ending Backlog1752                     1,274SEUC32024-20
5/11/20245/18/2024202420Ending Backlog1752                         734SEUC32024-20
5/11/20245/18/2024202420Ending Backlog1752                     1,353EEEUC22024-20
5/11/20245/18/2024202420Ending Backlog1752                     1,058U2EUC42024-20
5/11/20245/18/2024202420Ending Backlog1752                     1,331U1EUC42024-20
5/11/20245/18/2024202420Ending Backlog1752                     1,383AEUC12024-20
5/11/20245/18/2024202420Ending Backlog1752                     1,266BEUC12024-20
5/18/20245/25/2024202421Beginning Backlog1752                         380SEUC32024-21
5/18/20245/25/2024202421Beginning Backlog1752                     1,190SEUC32024-21
5/18/20245/25/2024202421Beginning Backlog1752                         669SEUC32024-21
5/18/20245/25/2024202421Beginning Backlog1752                         551SEUC32024-21
5/18/20245/25/2024202421Beginning Backlog1752                         950BEUC12024-21
5/18/20245/25/2024202421Beginning Backlog1752                           69BEUC12024-21
5/18/20245/25/2024202421Beginning Backlog1752                         159SEUC32024-21
5/18/20245/25/2024202421Beginning Backlog1752                             8SEUC32024-21
5/18/20245/25/2024202421Beginning Backlog1752                     1,078SEUC32024-21
5/18/20245/25/2024202421Beginning Backlog1752                     1,319BEUC12024-21
5/18/20245/25/2024202421Beginning Backlog1752                           12SEUC32024-21
5/18/20245/25/2024202421Beginning Backlog1752                         430SEUC32024-21
5/18/20245/25/2024202421Beginning Backlog1752                     1,848EEEUC22024-21
5/18/20245/25/2024202421Shipments1752                     1,383BEUC12024-21
Labels:
Message 1 of 2
355 Views
0
1 ACCEPTED SOLUTION
tomasjezek
Frequent Visitor

‎05-22-2024 03:09 AM

Ok,

 

I solved it eventually...

 

Bookings2 =

VAR _t =
    ADDCOLUMNS (
        SUMMARIZE (Append1, Append1[FY-FW]),
        "@bookings",
        CALCULATE (
            IF (ISBLANK([EndBacklog]),[Shipments],[EndBacklog]+[Shipments]-[BegBacklog])
        )
    )
RETURN
    SUMX ( _t, [@bookings])
 
 
...I reused the similar approach like in this topic:
 
Kudos @Jihwan_Kim 
 

View solution in original post

Message 2 of 2
339 Views
0
1 REPLY 1
tomasjezek
Frequent Visitor

‎05-22-2024 03:09 AM

Ok,

 

I solved it eventually...

 

Bookings2 =

VAR _t =
    ADDCOLUMNS (
        SUMMARIZE (Append1, Append1[FY-FW]),
        "@bookings",
        CALCULATE (
            IF (ISBLANK([EndBacklog]),[Shipments],[EndBacklog]+[Shipments]-[BegBacklog])
        )
    )
RETURN
    SUMX ( _t, [@bookings])
 
 
...I reused the similar approach like in this topic:
 
Kudos @Jihwan_Kim 
 
Message 2 of 2
340 Views
0

Helpful resources

Announcements
November Power BI Update Carousel

Power BI Monthly Update - November 2025

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

Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All