Forum Discussion
Stock month showing wrong values due to adding two fields into the table visual
Hi All
Stock month values showing wrong values when i add two fields into the table visual "Reorder Point","Minimum lot size"
if i remove two fields in to the table data showing correct, here date selection 2026-03,
I want to get green colour values when i add "Reorder point and Minimum lot size" fileds in the table, please suggest
Measure details -
Stock Month = DIVIDE([Total Value in Stock Local], [COGS 12 month moving average Local])
Total Value in Stock Local = SUM('Stock Healthiness'[Total Value in Stock Local_])
COGS 12 month moving average Local = DIVIDE(CALCULATE([Cost of Goods Sold Local],DATESINPERIOD('Calendar'[Date],LASTDATE('Calendar'[Date]),-12,MONTH)), 12)
Cost of Goods Sold = Switch(Values('Currency'[Currency]),"EUR",[Cost of Goods Sold EUR],"Local",[Cost of Goods Sold Local])
Hi BalajiBuddha,
The issue is that when you add these two columns in the table visual, they change the row level granualirity of the calculation and your measure Stock Month gets recalculated at a more detailed level.
To fix this your measure should ignore Reorder Point & Minimum Lot Size context.
Update your dax is such a way:-
Try below dax
Stock Month =
DIVIDE(
CALCULATE(
[Total Value in Stock Local],
REMOVEFILTERS('YourTable'[Reorder Point], 'YourTable'[Minimum Lot Size])
),
CALCULATE(
[COGS 12 month moving average Local],
REMOVEFILTERS('YourTable'[Reorder Point], 'YourTable'[Minimum Lot Size])
)
)Alternatively you can also achieve it with ALLEXCEPT function
Stock Month =
DIVIDE(
CALCULATE(
[Total Value in Stock Local],
ALLEXCEPT('Stock Healthiness', 'Stock Healthiness'[Material], 'Stock Healthiness'[Plant])
),
CALCULATE(
[COGS 12 month moving average Local],
ALLEXCEPT('Stock Healthiness', 'Stock Healthiness'[Material], 'Stock Healthiness'[Plant])
)
)If it still doesn;t work look at your relationship setup and try to use the correct relationship in your measure using USERELATIONSHIP() function.
🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!
7 Replies
Hi BalajiBuddha,
The issue is that when you add these two columns in the table visual, they change the row level granualirity of the calculation and your measure Stock Month gets recalculated at a more detailed level.
To fix this your measure should ignore Reorder Point & Minimum Lot Size context.
Update your dax is such a way:-
Try below dax
Stock Month =
DIVIDE(
CALCULATE(
[Total Value in Stock Local],
REMOVEFILTERS('YourTable'[Reorder Point], 'YourTable'[Minimum Lot Size])
),
CALCULATE(
[COGS 12 month moving average Local],
REMOVEFILTERS('YourTable'[Reorder Point], 'YourTable'[Minimum Lot Size])
)
)Alternatively you can also achieve it with ALLEXCEPT function
Stock Month =
DIVIDE(
CALCULATE(
[Total Value in Stock Local],
ALLEXCEPT('Stock Healthiness', 'Stock Healthiness'[Material], 'Stock Healthiness'[Plant])
),
CALCULATE(
[COGS 12 month moving average Local],
ALLEXCEPT('Stock Healthiness', 'Stock Healthiness'[Material], 'Stock Healthiness'[Plant])
)
)If it still doesn;t work look at your relationship setup and try to use the correct relationship in your measure using USERELATIONSHIP() function.
🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!Hi BalajiBuddha, you are most likely changing the filter context when adding those fields due to the change in granularity.
My suggestion would be to either lock the date context or remove unwanted filters.
Locking the date context would be something like:
COGS 12 month moving average Local = DIVIDE( CALCULATE( [Cost of Goods Sold Local], DATESINPERIOD( 'Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH ), ALL('Stock Healthiness') ), 12 )HI BalajiBuddha,
Have you had a chance to review the solution we shared by grazitti_sapna ,luisoliveira89? If the issue persists, feel free to reply so we can help further.
Thank you.
Thanks for your solution
HI,
After adding Reorder point and Minimum Lot Size i am getting duplicatesPlease give me correct calculation
Hi BalajiBuddha,
I have reproduced the issue using the sample data. Please find the attached PBIX file for your reference.
Thank you.
Hi BalajiBuddha,
Have you had a chance to review the solution we shared earlier? If the issue persists, feel free to reply so we can help further.
Thank you.
