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?
      • v-jingzhang's avatar
        v-jingzhang
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi Anonymous 

         

        Sorry I forgot the possibility of same consumption over different materials. Please use below measure for Total consumption. This would not have that error.

        Total consumption new =
VAR _table1 =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Plnt] = SELECTEDVALUE ( 'Table'[Plnt] )
            && 'Table'[MTyp] = SELECTEDVALUE ( 'Table'[MTyp] )
            && 'Table'[Month] = SELECTEDVALUE ( 'Table'[Month] )
    )
VAR _table2 =
    DISTINCT (
        SELECTCOLUMNS (
            _table1,
            "_Material", 'Table'[Material],
            "Consumption_valorization", 'Table'[Consumption valorization]
        )
    )
RETURN
    SUMX ( _table2, [Consumption_valorization] )

         

        Best Regards,

        Jing