Forum Discussion

santigc97's avatar
santigc97
Frequent Visitor
2 years ago

Related Collumns or Summarize for create a table

Hello,   I am having a problem with a new table that i want to create with the function related or summarize collumns   I have the following data on which for an item i have the DMD on each locat...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi santigc97 ,

Based on my testing, please try the following methods:

1.Create the simple table.

2.Create the new columns to calculate.

 

NEW DMD = 
VAR _loc='Table'[LOC]
var _new='Table'[NEW]
return CALCULATE(SUM('Table'[DMD]),FILTER(ALL('Table'),'Table'[LOC]=_loc&&'Table'[ITEM]=_new))
new+1 = 
VAR ite = 'Table'[ITEM]
VAR loc = 'Table'[LOC]
RETURN
IF(('Table'[NEW] + 1) <= MAX('Table'[ITEM]) && 'Table'[NEW] <> BLANK(), 'Table'[NEW] + 1, BLANK())
NEW+1 DMD = 
VAR _loc = 'Table'[LOC]
var _new = 'Table'[new+1]
return CALCULATE(SUM('Table'[DMD]),FILTER(ALL('Table'),'Table'[LOC]=_loc &&'Table'[ITEM] = _new))
OLD DMD = 
VAR _loc = 'Table'[LOC]
var _old = 'Table'[OLD]
return CALCULATE(SUM('Table'[DMD]),FILTER(ALL('Table'),'Table'[LOC]=_loc&&'Table'[ITEM]= _old))
OLD - 1 = 
VAR loc = 'Table'[LOC]
VAR old = 'Table'[OLD]
var _item = CALCULATE(MAX('Table'[ITEM]),FILTER(ALL('Table'),'Table'[LOC]= loc &&'Table'[ITEM] = old - 1))
return 
IF(NOT(ISBLANK('Table'[OLD]))&&NOT(ISBLANK(_item)),'Table'[OLD]-1)

 

3.The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.