Hello again @v-lili6-msft
Thank you for your help.
I read the topic, that you sugested. If I get it right you want to group my data in some way to make it easier to calculate, but I dont really know how to do it. Maybe i will show you how my dataset is made and how does my measure work, so you will have better understanding for my problem.
First I had a table, that had distinct values of 2 columns - material number in first column and company code in second.
Every row had unique connection of company code and material number, and a third column of prize.
Then I expanded my table -> for every row i added a columns with grouping data alike brand or type, but also every row I expanded for few attributes(freshness of material) with max of 21 rows (0,5,10,15,...,100) and a value of quantity in that freshness category.
Third step was merging this table with another table with had the same material/company code but also it has a demand for every week in horizon(78weeks from now) and a value of it. So every row that had material/companycode combo with freshness and value was multiplied up to 78 times because of the demand column.
My measure takes freshnes of aging stock dynamically (lets take 60 - 80) and sums up quantity of aging stock. Then it takes dynamically chosen number of weeks (lets take 10) and for every companycode/material it sums up demand value. If we have more aging stock then demand we will probobaly wont sell it and it will get old, so we lost money, and for every material/companycode that has more aging stock than demand we multiply by price. And that what my measure do and i want to sum it up somehow.
Below I put my measure in better view.
Bad Goods Projected = [BG Projected]*CALCULATE(SUM('Merge1'[Price]), ALLEXCEPT('Merge1','Merge1'[Material],'Merge1'[Company code]),'Merge1'[Plant])/CALCULATE(COUNTROWS('Merge1'),ALLEXCEPT('Merge1','Merge1'[Material],'Merge1'[Company code],'Merge1'[Plant]))
BG Projected = IF([Aging Stock]-[Demand]<0,0,[Aging Stock]-[Demand])
Aging Stock = CALCULATE(SUM('Merge1'[FreshNes of Inventory.Value]),ALLEXCEPT('Merge1','Merge1'[Material],'Merge1'[Company code],'Merge1'[Plant],'Merge1'[FreshNes of Inventory.Attribute2]),FILTER('Merge1','Merge1'[FreshNes of Inventory.Attribute2]>=[Min Aging Value]),FILTER('Merge1','Merge1'[FreshNes of Inventory.Attribute2]<[Max Aging Value]))/CALCULATE(COUNTROWS('Merge1'),ALLEXCEPT('Merge1','Merge1'[Material],'Merge1'[Company code],'Merge1'[Plant],'Merge1'[FreshNes of Inventory.Attribute2]),FILTER('Merge1','Merge1'[FreshNes of Inventory.Attribute2]>=[Min Aging Value]),FILTER('Merge1','Merge1'[FreshNes of Inventory.Attribute2]<[Max Aging Value]))*CALCULATE(DISTINCTCOUNT('Merge1'[FreshNes of Inventory.Attribute2]),ALLEXCEPT('Merge1','Merge1'[Material],'Merge1'[Company code],'Merge1'[Plant]),FILTER('Merge1','Merge1'[FreshNes of Inventory.Attribute2]>=[Min Aging Value]),FILTER('Merge1','Merge1'[FreshNes of Inventory.Attribute2]<[Max Aging Value]))
Demand = CALCULATE(SUM('Merge1'[Stock Location - Demand.Value]),ALLEXCEPT('Merge1','Merge1'[Material],'Merge1'[Company code],'Merge1'[Plant]),FILTER('Merge1','Merge1'[Stock Location - Demand.Week]<=[ActWeek]+[LAG]))/3
My question is does that makes sense to you and do you think i could do it faster/better/other way so i can calculate sum of this
Thank you once again
Best Regards
Radkos
