Measure of measure

[GianlucaM]

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

[v-jingzhang]

Hi @GianlucaM 

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.

[GianlucaM]

Hi,

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

[v-jingzhang]

Hi @GianlucaM 

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.

[GianlucaM]

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]

Hi @GianlucaM 

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

[GianlucaM]

Hi, 

this seem much better now but I've to notice that this works just if we work on a single table.

As you can imagine I can have in my model some filtert table aside. 

For example, material Type or Month are actually part of a wider model that I need to filter through "star" table that drive all my model.
I have notice that this formula doesn't work with those table filter.

How can I manage this complication?

Beside that I have to notice even that I can do this job group by group but it doesnt in an overall view: you can see from this screen, In your eveluation can you even consider what it takes to make it work even from an overall point of view? this would be great!

Thanks

[v-jingzhang]

Hi @GianlucaM 

Are meterial type and month from some Dim tables? And you will use fields from Dim tables to filter the report? Can you provide a small example model of that as well as the relationships? And what is the current result with those table filters and what is the expected result?

Jing

[GianlucaM]

Hi,

yes I use Dim table to filter the report so i would need to set up measure basing on the star filter Dim tables.

Beside that I need to have a resoult that work even on total (the second question here over)

Thanks

[v-jingzhang]

Hi @GianlucaM 

The measure should also work in this model. Can you show its current result and expected result by taking some data as example?

If you use columns from Dim tables in the table visual, you can try using these columns in SELECTEDVALUE function to replace corresponding columns from Fact table. But as the tables are related, maybe the result is same.  

Jing

[GianlucaM]

Hi,

actually the model is working even with filter on DIM table. The behaviour I'm not still able to obtain is the following.

I'm able to have the rotation index over a single family but, if I select all the family or even just two it doesn't give me back the index.
In my model I have the "Plant" in which I have many families: what I need to have is a total consumpion (and as a consequence the rotation index) even for the sum of all the families.

Here the example of what I've: with one family it works

With two or more it stops to return me the desired value of total consumption and IR as a consequence

please help me to solve this

[GianlucaM]

Hi,

is there any update I can have here? still no answer from a long time. If no solution can be provided I need to re think my model

Thanks

[lbendlin]

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.

[GianlucaM]

https://docs.google.com/spreadsheets/d/1qTHkNwE-demv9mX7WlCkBRdsmMeScf2w/edit#gid=26676764 

Hi, in the attached file I hope you can get where I have to start and where I want to obtain

Thanks

Gianluca