cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
StidifordN Regular Visitor
Regular Visitor

Calculated Table, remove duplicates resulting from MAX of field

I have a calculated table to return a supplier and their levels of categorisation (to be looked up to in another table). Problem is our raw data has multiple categories/sub categories for a single supplier that i want reduced to a single category/sub category per supplier.

It is possible to amend the calculated table formula to remove all duplicate BUT leaving the entry that is the highest spend (max).  All unique entries to remain unaffected.

 

SupplierCats = 
FILTER(
  SUMMARIZE(APAll,APAll[PTRN_SUPP],APAll[SpendCategory],APAll[SpendSubCategory],"Count",COUNT(APAll[PTRN_SUPP]),"Value",MAX(APAll[NET_AMOUNT_AU])),
  APAll[SpendCategory]<>BLANK()
)

The highlighted yellow suppliers below are duplicates - the item with the highest spend is the entry that should remain out of the 3.

Capture987.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Calculated Table, remove duplicates resulting from MAX of field

Hi @StidifordN,

 

For you scenario, you could create another measure then you could achieve your desired output.

 

tag =
IF (
    CALCULATE ( SELECTEDVALUE ( 'SupplierCats'[Value] ) )
        = CALCULATE (
            MAX ( 'SupplierCats'[Value] ),
            ALLEXCEPT ( SupplierCats, SupplierCats[PTRN_SUPP] )
        ),
    1,
    0
)

Then drag this measure to visual level filter and show the item when value is 1.

 

measure.PNG

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Calculated Table, remove duplicates resulting from MAX of field

Hi @StidifordN,

 

For you scenario, you could create another measure then you could achieve your desired output.

 

tag =
IF (
    CALCULATE ( SELECTEDVALUE ( 'SupplierCats'[Value] ) )
        = CALCULATE (
            MAX ( 'SupplierCats'[Value] ),
            ALLEXCEPT ( SupplierCats, SupplierCats[PTRN_SUPP] )
        ),
    1,
    0
)

Then drag this measure to visual level filter and show the item when value is 1.

 

measure.PNG

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
StidifordN Regular Visitor
Regular Visitor

Re: Calculated Table, remove duplicates resulting from MAX of field

Mate - perfect!

For my purpose I applied this as a column and the result was still spot on.