Forum Discussion
sum or count a measure
Thanks for that.
It looks like the problem is the VAR/RETURN syntax.
If you use VAR to store the result of your existing measure like this, it actually just evaluates once and locks in a single value, evaluated in the overall filter context (i.e. not per week).
We need the validobservation measure to be re-evaluated for every iteration of the SUMX, so the SUMX should refer to a measure (or equivalent expression wrapped in CALCULATE), not a variable.
I would say the cleanest way to do this is to keep your first measure as you have defined it (since you know it's working at the Week level), and call it say validobservation.
Then your new measure should be:
Valid Week Count = SUMX ( VALUES ( TimeTable[Week] ), [validobservation] )That is, the second argument of the SUMX should be a measure, not a variable.
Does that help?
Regards,
Owen
Super UserHi liran
You can use SUMX to iterate over weeks and sum your existing measure (since it is a binary flag).
Based on the code you posted, if we call your existing measure [Week valid flag], then your new measure should be something like:
Valid Week Count =
SUMX (
VALUES ( TimeTable[Week] ),
[Week valid flag]
)I'm assuming TimeTable[Week] is the column that defines the weeks you want to iterate over.
Hopefully that helps
Regards,
Owen :)
hi owen,
thank you for your answer but it didn't work
the first pivot shows the measre when weeks are on the rows, with the last column as total that i calculated manually.
the lower pivot shows the measure when weeks are not in the rows, and the measure as you suggested, both don't give me the correct total that i'm looking for
- OwenAuger
Thanks for the update liran
Just to confirm, could you please post the code for both your original measure and the new measure you created with SUMX?
Also could you post the structure of each table (Query and TimeTable) and the relationship between the two - just to check what I missed.
Regards,
Owen
they related in a on to many where the TimeTable has uniqe values of weeks and the query has weeks for each store and item.
the first measure is the original that i'm using and the seceond is the measure with the sumx.
i'll try to explain more about the measure:
i check for each item and store how many weeks are valid, and a valid means that the item was sold in any store of the database
and that the store sold any other item at this week. means it's valid and i want to count those weeks for each combination of
item and store.
the problem is that when there is no sell for an item in a store at a certain week i don't have a row for this in the data
wich makes it much harder to count the number of weeks it was valid.
i hope i was more clear now about the problem i have.
thank you
