Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 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 value of the stock to the Consumption valorization.

Unfortunately the Consumption valorization is a lable value so it is repeated by any row. I'm looking for a solution (without grouping) that makes me possible to aggregate the consumption and the Value by material type or any other group I may need.

The issue here is that for any row I need to divide the consumption for the nr of rows of the single Item and them Sum this (average) in order to divide the same for the total value.


I don't know how to do it as, I can calculate thanks to a Measure in Power BI the Rotation index for the single Item, but do the IR for families that aggregate 

 

Hope someone can help!

Thabn

Need Help

13 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    Please provide sanitized sample data in usable format (not as a picture - inserting it into a table would be good) and show the expected outcome.

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 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
      4 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
        4 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.