Forum Discussion

liran's avatar
liran
Frequent Visitor
8 years ago
Solved

sum or count a measure

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's avatar
    OwenAuger
    8 years ago

    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

6 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    8 years ago

    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's avatar
      liran
      Frequent Visitor
      8 years ago

      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's avatar
        OwenAuger
        Icon for Super User rankSuper User
        8 years ago

        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