Find the max of a column with filters from other columns

[stino123]

Hey,

I have a column in my shopify data that compiles price and discount price as line price. I want to calculate total discount, to do so based on the unique barcode return the max value (line price) for a given barcode. I will after subtract the difference of the new column and "line price" to calculate discounts. However my formula (below) will generate the row value rather than the max value for a given barcode. Added complication I need to exclude all blank values in column barcode. 

Dataset

Column A: Line price

Column B: Barcode

New column C (objective) find max value for any given barcode 

=CALCULATE(max(FACT_Orders[Line Price]),DISTINCT(FACT_Orders[Barcode]),FACT_Orders[Barcode]<> BLANK())

Could someone please help me out?

Thanks in advance!

[ValtteriN · Accepted solution]

Hi,

You can achieve this with e.g. ALLEXCEPT:

=CALCULATE(max(FACT_Orders[Line Price]),ALLEXCEPT(FACT_Orders[Barcode]))
The idea here is to remove all the other filters except for the barcode and get the max value.


I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

[stino123]

Hey Valtteri,

This helped allot, for those products that don't have barcodes how do you exclude it from the equation? since now the few products that have "blank" cells  within the column "barcode" all have the same price. 

[stino123]

Figured it out: 

Full price = IF(FACT_Orders[Barcode]<> BLANK(),

CALCULATE(max(FACT_Orders[Line Price]),

ALLEXCEPT(FACT_Orders,FACT_Orders[Barcode])),

FACT_Orders[Line Price])

Thanks again for the help 🙂