Forum Discussion

Didik62's avatar
Didik62
Frequent Visitor
3 years ago
Solved

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

Hi,

maybe it seems like too basic.

But i need hel to figure it out

 

my data :

DateProductCustomerOrderStock
2/3/2023A001750
2/3/2023A0021250
2/3/2023A003250
2/3/2023B002523
2/3/2023B0051223
2/4/2023A006447
2/4/2023A007547
2/4/2023B002731
2/5/2023B005631

 

and i need to summarized it into

DateProductOrderStock
2/3/2023A2150
2/3/2023B1723
2/4/2023A947
2/4/2023B1331

 

to get Stock and total order by product, by date

 

Thank you

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

     

  • Manoj_Nair's avatar
    Manoj_Nair
    3 years ago

    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])
        )
    )
)

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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

     

  • Manoj_Nair's avatar
    Manoj_Nair
    Solution Supplier
    3 years ago

    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's avatar
      Manoj_Nair
      Solution Supplier
      3 years ago

      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])
        )
    )
)
  • Didik62's avatar
    Didik62
    Frequent Visitor
    3 years ago

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