sum or count a measure

[liran]

hi, 

i have a measure that calculate if a certain week is valid for each store and item

when i put the weeks in the rows of my excel sheet i see the desirable result and every valid week gets 1 and the others get null.

now i want to count the number of weeks that are valid using a measure and i don't succeed.

anyone has an idea how can i calculate it?

thanks

[OwenAuger]

Hi @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 🙂

[liran]

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

[liran]

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

[OwenAuger · Accepted solution]

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

[liran]

yes it works well now! 

thank you very much