Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
stino123
Helper II
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())

Could someone please help me out?

 

Thanks in advance!

 

1 ACCEPTED SOLUTION
ValtteriN
Super User
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!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
ValtteriN
Super User
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!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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. 

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 🙂 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors