Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Control the first row values in a Matrix

I have a matrix that displays available-to-sell quantities, indicating both current on-hand inventory and projected future availability. At the row level, data is broken down by incoming dates, with a running total of quantities across sizes shown in columns. However, when rolling up to the product level, the values are sum up across all rows, leading to inaccurate totals.

Ideally, I want the matrix should show only the on-hand quantity at the product level when not expanded, while allowing drill-down to reveal incoming dates and projected availability.

 

 

Thank you

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi All,
    Firstly  vojtechsima thank you for you solution!

    And Anonymous ,You want to show only the OnHand quantity before the ATP Date, and then count the number of items arriving after the ATP Date, right?

    If this is the case, we can use the isinscope function to help us fulfill this requirement.

    DisplayedTotal = 
IF(
    ISINSCOPE('Table'[Lead_Time]), 
    SUM('Table'[Value]),
    IF(ISINSCOPE('Table'[ItemGroup]),
    CALCULATE(SUM('Table'[Value]),'Table'[ATP_Date]="OnHand"),
    SUM('Table'[Value])
    
))

    I hope this solves your problem, if you have any other questions you can check out the pbix file I uploaded, I would be honored if it solves your problem!

     

    Hope it helps!

    Best regards,
    Community Support Team_ Tom Shen

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.


     

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

    It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered? If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
    Looking forward to your reply!

4 Replies

  • vojtechsima's avatar
    vojtechsima
    Super User
    1 year ago

    Hello, Anonymous ,

    you can try playing with HASONEVALUE,

    have a measure that is something similar to 

     

    IF ( HASONEVALUE('table'[product_level]), [regular_measure], CALCULATE( [regular_measure], 'table'[ATP_Date-copy]="OnHand") )

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi All,
    Firstly  vojtechsima thank you for you solution!

    And Anonymous ,You want to show only the OnHand quantity before the ATP Date, and then count the number of items arriving after the ATP Date, right?

    If this is the case, we can use the isinscope function to help us fulfill this requirement.

    DisplayedTotal = 
IF(
    ISINSCOPE('Table'[Lead_Time]), 
    SUM('Table'[Value]),
    IF(ISINSCOPE('Table'[ItemGroup]),
    CALCULATE(SUM('Table'[Value]),'Table'[ATP_Date]="OnHand"),
    SUM('Table'[Value])
    
))

    I hope this solves your problem, if you have any other questions you can check out the pbix file I uploaded, I would be honored if it solves your problem!

     

    Hope it helps!

    Best regards,
    Community Support Team_ Tom Shen

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.


     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Thank you so much ! This works, I apologise for the delayed response

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,

    It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered? If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
    Looking forward to your reply!