Control the first row values in a Matrix

[vedika1089]

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

[Anonymous · Accepted solution]

Hi All,
Firstly  vojtechsima thank you for you solution!

And @vedika1089 ,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 · Accepted solution]

Hi @vedika1089 ,

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!

[Anonymous · Accepted solution]

Hi All,
Firstly  vojtechsima thank you for you solution!

And @vedika1089 ,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.

[vedika1089]

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

[vojtechsima]

Hello, @vedika1089 ,

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