Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Measure of measure

Hi All,   I have a list of Item (thousands) in stock and for any of them I can have different store location. In order to calculate the Rotation index of warehouse I need to compare the Total valu...
Need Help
v-jingzhang's avatar
v-jingzhang
Icon for Community Support rankCommunity Support
5 years ago

Hi Anonymous 

 

Your file requires logging in, can you share it again to public or provide a sample output with sample data?

 

As of now, if I understand it correctly, you could create a calculated column with below code to get the average value of consumption by material and month. 

average of consumption =
VAR nr_of_rows =
    COUNTROWS (
        FILTER (
            'table',
            'table'[Material] = EARLIER ( 'table'[Material] )
                && 'table'[Month] = EARLIER ( 'table'[Month] )
        )
    )
RETURN
    DIVIDE ( 'table'[Consumption valorization], nr_of_rows )

 

But for "Sum this (average) in order to divide the same for the total value", I am not sure what that is so I don't create a formula. 

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi,

     

    now the file is public, sorry for the incovenient. Please check it and let me know whether you can have access

     

    • v-jingzhang's avatar
      v-jingzhang
      Icon for Community Support rankCommunity Support
      5 years ago

      Hi Anonymous 

       

      Try these measures:

      Total value = CALCULATE(SUM('Table'[Tot value]),ALLEXCEPT('Table','Table'[Plnt],'Table'[MTyp],'Table'[Month]))
      Total consumption = SUMX(CALCULATETABLE(DISTINCT('Table'[Consumption valorization]),ALLEXCEPT('Table','Table'[Plnt],'Table'[MTyp],'Table'[Month])),'Table'[Consumption valorization])
      IR = DIVIDE([Total consumption],[Total value])

       

      Best Regards,
      Community Support Team _ Jing
      If this post helps, please Accept it as Solution to help other members find it.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Thanks for the feedback but I need to understand it better as in my file seems it doesn't work that fine.

        If I am right, SUMX consider to sum all the rows that has dintinct values in the 

        [Consumption valorization] while this might drive to an error if I have same consumption over different code. Better would be to consider just rows that have the combination of [Plant]+[Month]+[Material]. In other words I would prefer to not count rows that have the same concatenation of [Plant]+[Month]+[Material]. How can I perform this?