cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rasty Regular Visitor
Regular Visitor

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

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Subtracting rows

@Rasty ,

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.
6 REPLIES 6
Moderator v-yuezhe-msft
Moderator

Re: Subtracting rows

@Rasty ,

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.
Rasty Regular Visitor
Regular Visitor

Re: Subtracting rows

Hi Lydia,

 

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

 

Capture1.JPG

 

//Rasty

Moderator v-yuezhe-msft
Moderator

Re: Subtracting rows

@Rasty ,

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.
Moderator v-yuezhe-msft
Moderator

Re: Subtracting rows

@Rasty ,

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.
Rasty Regular Visitor
Regular Visitor

Re: Subtracting rows

Hi Lydia,

 

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

Capture.JPG

Thanks.

 

Moderator v-yuezhe-msft
Moderator

Re: Subtracting rows

@Rasty ,

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.