The error message you're encountering, "A single value for column 'Material group' in table 'Sales Data' cannot be determined," indicates that your DAX measure is trying to use a column in a context where it expects a single value, but it's getting multiple values. This often happens when you're using a measure in a way that requires aggregation at a lower granularity level.
In your PTmodelling measure, you are using several IF statements, and each of them works within a filter context. When you filter your data based on material groups, the measure needs to return a single value for each row in the Sales Data table. However, your PTmodelling measure doesn't do that, which is causing the error.
To resolve this issue, you should modify your PTmodelling measure to return a single value for each row in the Sales Data table. You can achieve this by aggregating the values for each material group using functions like SUMX, MAXX, or MINX. Here's an updated version of your PTmodelling measure using SUMX:
x Product Tonne modeling =
SUMX(
'Sales Data',
VAR MaterialGroup = 'Sales Data'[Material group]
RETURN
SWITCH (
TRUE (),
MaterialGroup = "Urea Granular", GUreduction,
MaterialGroup = "Coated Urea", CoatedUrea,
MaterialGroup = "Urea Blends", UBreduction,
MaterialGroup = "Coated Blends", CoatedUB,
MaterialGroup = "EEF Blends", EEFBlendIncrease,
'Sales Data'[Product Tonne]
)
)
This updated measure uses SUMX to iterate over each row in the Sales Data table and applies the calculations based on the Material Group. Make sure your other measures (GUreduction, CoatedUrea, UBreduction, CoatedUB, EEFBlendIncrease) are correctly defined as well.
Remember to ensure that your parameter values are correctly defined and that the data model is set up properly for these calculations to work as intended.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
