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: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register 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
265 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
249 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
250 Views
0

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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

View All