Forum Discussion

otter1's avatar
otter1
New Member
3 years ago
Solved

Column with one value per group

Hello,   I have a table with very small granularity: Category 1 Category 2 Category 3 Category 4 Sales A X 1 202 400 A X 1 203 400 B Y 2 109 650 B Y 2 204 650 ...
General Comment
Need Help
Show and Tell
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, otter1 

     

    You can try the following methods.

    Column = 
Var _minCat4=CALCULATE ( MIN ( 'Table'[Category 4] ),
            ALLEXCEPT ( 'Table', 'Table'[Category 1] ) ) 
Return
IF ( [Category 4] = _minCat4,
    CALCULATE ( MAX ( 'Table'[Sales] ),
        FILTER ('Table',
            [Category 1] = EARLIER ( 'Table'[Category 1] )
            && [Category 2] = EARLIER ( 'Table'[Category 2] )
            && [Category 3] = EARLIER ( 'Table'[Category 3] )
        )
    ),
    BLANK ()
)

    Is this the result you expect?

    Best Regards,

    Community Support Team _Charlotte

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

TomasAndersson's avatar
TomasAndersson
Icon for Solution Sage rankSolution Sage
3 years ago

Hi!
I assumed you wanted to keep the lowest Category 4 in each case, if not you will have to adjust accordingly, but something like this should work.

New Sales = 
VAR __Cat1 = Sales[Category 1]
VAR __Cat2 = Sales[Category 2]
VAR __Cat3 = Sales[Category 3]
VAR __Cat4 = Sales[Category 4]
VAR __Sales = Sales[Sales]
return
CALCULATE(
    if(__Cat4 = min(Sales[Category 4]), __Sales),
    FILTER(
        ALL(Sales),
        Sales[Category 1] = __Cat1 &&
        Sales[Category 2] = __Cat2 &&
        Sales[Category 3] = __Cat3
    )
)


Good luck!