Stock month showing wrong values due to adding two fields into the table visual

[BalajiBuddha]

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

[BalajiBuddha]

HI,

After adding Reorder point and Minimum Lot Size i am getting duplicates

Please give me correct calculation

[v-saisrao-msft]

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.

[v-saisrao-msft]

Hi @BalajiBuddha,

I have reproduced the issue using the sample data. Please find the attached PBIX file for your reference.

Thank you.

[BalajiBuddha]

Thanks for your solution

[v-saisrao-msft]

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.

[luisoliveira89]

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
)

[grazitti_sapna · Accepted solution]

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!