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 1Category 2Category 3Category 4Sales
AX1202400
AX1203400
BY2109650
BY2204650

The Sales are duplicated based on 4 categories, but I would like a column that would only have 1 row filled for category 1,2,3:

Category 1Category 2Category 3Category 4SalesNew Sales
AX1202400400
AX1203400 
BY2109650650
BY2204650 

 

How can I achieve this? Any help would be greatly appreciated.

 

BR,

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.

2 Replies

  • 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!

     

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    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.