Sum measure values from specific table

Question

Hello all,I have got a problem. I wrote a measure using 3 different tables to predict costs for products in future.My measure uses&nbsp;about 5&nbsp;calculate functions with allexcepts and distinct countrows&nbsp;and filters and so on, so i can not just sum it up so easily. My question is, if i have table, where i have all data needed to make my measure work, can i simply take that cell from table?&nbsp;

v-lili6-msft · Answer

hi,&nbsp;Anonymous&nbsp; &nbsp; &nbsp;You can get&nbsp;it, but you need to use VAR Function to create a dynamic table firstly.&nbsp;Best Regards,Lin

anonymous · Answer

Hello, v-lili6-msftI am intrested, can you help me with creating that table? And will it be slower or faster?Thank you for sharing the ideaBest RegardsRadkos

v-lili6-msft · Answer

hi,&nbsp;Anonymous&nbsp; &nbsp; &nbsp;This requires specific data and requirements to be analyzed. this case can help you understand it.https://community.powerbi.com/t5/Desktop/Group-Measure-values-on-rows/m-p/514475#M240454&nbsp;Best Regards,Lin

anonymous · Answer

Hello again v-lili6-msftThank 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&nbsp;a third column of prize.Then I expanded my table -&gt; for every row i added a columns with grouping data alike brand or type, but also every row&nbsp;I&nbsp;expanded for&nbsp;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&nbsp;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.&nbsp;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]))&nbsp;BG Projected = IF([Aging Stock]-[Demand]&lt;0,0,[Aging Stock]-[Demand])&nbsp;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]&gt;=[Min Aging Value]),FILTER('Merge1','Merge1'[FreshNes of Inventory.Attribute2]&lt;[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]&gt;=[Min Aging Value]),FILTER('Merge1','Merge1'[FreshNes of Inventory.Attribute2]&lt;[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]&gt;=[Min Aging Value]),FILTER('Merge1','Merge1'[FreshNes of Inventory.Attribute2]&lt;[Max Aging Value]))&nbsp;Demand = CALCULATE(SUM('Merge1'[Stock Location - Demand.Value]),ALLEXCEPT('Merge1','Merge1'[Material],'Merge1'[Company code],'Merge1'[Plant]),FILTER('Merge1','Merge1'[Stock Location - Demand.Week]&lt;=[ActWeek]+[LAG]))/3&nbsp;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 thisThank you once againBest RegardsRadkos

v-lili6-msft · Answer

hi,&nbsp;AnonymousI'm a little confused about your description, could you please do and share a simple sample pbix&nbsp;and expected output for us.You can upload it to OneDrive or Dropbox and post&nbsp;the link here. Do mask sensitive data before uploading.&nbsp;&nbsp;Best Regards,Lin

Forum Discussion

Sum measure values from specific table

8 Replies

Related content

Make measure ignore specific filter

Measure returning incorrect value

Fabric Notebook ExitValue how to select specific values

Dax Measure to get specific text value

Specific values in a slicer