Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Subtracting rows

Hi All,

 

I would like to subtract lines in Power BI.

 

Substract lines based on product id and ship date. "After shipping "is calculated column which values are correct only for 1 line.

Capture2.JPG

 

I would like to get below result.

 

Capture.JPG

Let me know if you need any informations.

 

1 ACCEPTED SOLUTION

@Anonymous ,

We don't need to create Table2. Please group Material in Power BI Desktop query editor, then create the following columns in table1. For more details, please check attached PBIX file.

shipcum = CALCULATE(SUM (Table1[To Ship] ),
    ALLEXCEPT( Table1,Table1[Material]),
    Table1[Index] <= EARLIER ( Table1[Index] ))
Column = CALCULATE(MAX(Table1[Inventory on Hand]),ALLEXCEPT(Table1,Table1[Material]),Table1[Index]=1)
After shipping = Table1[Column]-Table1[shipcum]



Regards,
Lydia

Community Support Team _ Lydia Zhang
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

6 REPLIES 6
v-yuezhe-msft
Employee
Employee

@Anonymous ,

Please create the following columns in your table.

Column = 
CALCULATE (
    SUM ( Table[To ship] ),
    ALLEXCEPT( Table,Table[Product]),
    Table[Date] <= EARLIER (  Table[Date] )
)
after shipping = Table[Inventory]-Table[Column]

1.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi Lydia,

 

It looks like below, using above. Any idea what is wrong?

 

Capture1.JPG

 

//Rasty

@Anonymous ,

Please firstly create a new table using dax below.

Table2 = SUMMARIZE(Table1,Table1[Material],Table1[Ship Date],"To ship all",SUM(Table1[To Ship]),"Inventory on Hand all", MAX(Table1[Inventory on Hand]))


Create the following columns in Table2.

mergecol = Table2[Material]&Table2[Ship Date]
Column = 
CALCULATE (
    SUM ( Table2[To ship all] ),
    ALLEXCEPT( Table2,Table2[Material]),
    Table2[Ship Date] <= EARLIER ( Table2[Ship Date] )
)

 

after shipping = Table2[Inventory on Hand all]-Table2[Column]


Create the following column in Table1.

mergecol = Table1[Material]&Table1[Ship Date]


Create relationship between tables using mergecol field. Then create a table visual as below. For more details, please check attached PBIX file.
1.png


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi Lydia,

 

This is very impresive, very close but, I would desire more as below.

Capture.JPG

Thanks.

 

@Anonymous ,

We don't need to create Table2. Please group Material in Power BI Desktop query editor, then create the following columns in table1. For more details, please check attached PBIX file.

shipcum = CALCULATE(SUM (Table1[To Ship] ),
    ALLEXCEPT( Table1,Table1[Material]),
    Table1[Index] <= EARLIER ( Table1[Index] ))
Column = CALCULATE(MAX(Table1[Inventory on Hand]),ALLEXCEPT(Table1,Table1[Material]),Table1[Index]=1)
After shipping = Table1[Column]-Table1[shipcum]



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@Anonymous ,

Could you please export the data of your table to Excel and share me the Excel? You can replace confidential data with dummy data.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors