Need Help. How to get single value from duplicate data but filter by another

[Didik62]

Hi,

maybe it seems like too basic.

But i need hel to figure it out

my data :

Date	Product	Customer	Order	Stock
2/3/2023	A	001	7	50
2/3/2023	A	002	12	50
2/3/2023	A	003	2	50
2/3/2023	B	002	5	23
2/3/2023	B	005	12	23
2/4/2023	A	006	4	47
2/4/2023	A	007	5	47
2/4/2023	B	002	7	31
2/5/2023	B	005	6	31

and i need to summarized it into

Date	Product	Order	Stock
2/3/2023	A	21	50
2/3/2023	B	17	23
2/4/2023	A	9	47
2/4/2023	B	13	31

to get Stock and total order by product, by date

Thank you

[Anonymous · Accepted solution]

Hello @Didik62 
Please follow these steps
Step 1) Make a table of distinct product 

  if you want to create that from the main data table you can do following 

select new table 

then write this dax formula in the new table measure 

product = DISTINCT(Sheet1[Product])

make sure to edit the name of your table in place of sheet1

Step 2:  Make Relationship between both the tables 

Step 3 :

Then make your visual 

Following is google drive link to the power bi file 

https://drive.google.com/file/d/1P3Gbb4sLo-1WsG3i41xnxZDO3h3ZxfAM/view?usp=sharing

Please use this file to follow the steps 
Please tag me using @ and reply if you need more help or have any question 

happy to help

Please accept this as solution and like the post if you are benefitted from this solution 

Thanks 

Sujit

[Didik62]

Thank you for your help guys. both solutions are works for me

[Manoj_Nair]

@Didik62- Hi, first you create these two DAX

1. TotalOrders = SUM('YourTable'[Order])

2. LastStock = 
CALCULATE (
    LASTNONBLANK ( 'YourTable'[Stock], [TotalOrders] ),
    ALL ( 'YourTable'[Order] )
)

then you create a summary table in Power BI using these measures:

1. Drag the 'Date' and 'Product' fields to the 'Values' area of your table visual.
2. Drag the TotalOrders and LastStock measures to the 'Values' area of your table visual.

Let me know if this works.

If this post helps to find solution would be happy if you could mark my post as a solution and give it a thumbs up

Best regards

Manoj Nair
Linkedin - https://www.linkedin.com/in/manoj-nair-%E2%98%81-344666104/

[Manoj_Nair · Accepted solution]

@Didik62or else try use this SUMMARISE DAX.

SummaryTable = 
ADDCOLUMNS(
    SUMMARIZE(
        YourTable, 
        YourTable[Date], 
        YourTable[Product], 
        "TotalOrders", SUM(YourTable[Order])
    ),
    "LastStock", 
    CALCULATE(
        LASTNONBLANK(YourTable[Stock], YourTable[Order]),
        FILTER(
            YourTable, 
            YourTable[Date] = EARLIER(YourTable[Date]) && 
            YourTable[Product] = EARLIER(YourTable[Product])
        )
    )
)

[Anonymous · Accepted solution]

Hello @Didik62 
Please follow these steps
Step 1) Make a table of distinct product 

  if you want to create that from the main data table you can do following 

select new table 

then write this dax formula in the new table measure 

product = DISTINCT(Sheet1[Product])

make sure to edit the name of your table in place of sheet1

Step 2:  Make Relationship between both the tables 

Step 3 :

Then make your visual 

Following is google drive link to the power bi file 

https://drive.google.com/file/d/1P3Gbb4sLo-1WsG3i41xnxZDO3h3ZxfAM/view?usp=sharing

Please use this file to follow the steps 
Please tag me using @ and reply if you need more help or have any question 

happy to help

Please accept this as solution and like the post if you are benefitted from this solution 

Thanks 

Sujit