cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

## Find the max of a column with filters from other columns

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())

1 ACCEPTED SOLUTION
Super User

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!

Proud to be a Super User!

3 REPLIES 3
Super User

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!

Proud to be a Super User!

Helper II

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.

Helper II

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 🙂

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors