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: 

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

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
112 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
96 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
97 Views
0

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

View All