Forum Discussion
Please help: Condition Column (has sample)
You can download the SAMPLE FILE created for you.
Thanks for the help. But the view is not what the result should look like. Based on your file, if I filter "Down 1 Month", customer 10016 shows. However, 10016's October weight is 4737 which is greater than September's weight 2759,54. The condition should be "Good"
In this scenario, since you want this condition column appear in matrix, this column should repeat with each month in your raw data. For your requirement, you need to create calcualted column for "last Month Weight", "last second Month Weight" and "last third Month Weight". Then use those three column as condition to create the "condition" column. Please refer to my sample below:
I use my own sample table.
1. Create a calculated column to return the max month within each Name group.
max Month = CALCULATE(MAX(Table1[Month]),ALLEXCEPT(Table1,Table1[Name]))
2. Create three calculated columns for "last Month Weight", "last second Month Weight" and "last third Month Weight".
last Month Value = LOOKUPVALUE(Table1[Value],Table1[Month],Table1[max Month],Table1[Name],Table1[Name])
last second Month Value = LOOKUPVALUE(Table1[Value],Table1[Month],Table1[max Month]-1,Table1[Name],Table1[Name])
last third Month Value = LOOKUPVALUE(Table1[Value],Table1[Month],Table1[max Month]-2,Table1[Name],Table1[Name])
3. Create condition column based above three calculated columns.
Status = IF(Table1[last Month Value]=BLANK(),"Non",IF(Table1[last Month Value]<Table1[last second Month Value] && Table1[last second Month Value]<Table1[last third Month Value],"down 2",IF(Table1[last Month Value]<Table1[last second Month Value],"down 1","good")))
The table looks the like below:
When putting fields into matrix, it will appear as you expected.
