Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
Hi,
now the file is public, sorry for the incovenient. Please check it and let me know whether you can have access
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.
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
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
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
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
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
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
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
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.