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

How to create colum with max values with conditions

Hi dear friends.

I need add a column with a max value for sales taking as conditions three fields. Basically, i need the maximum value for sales by turno, planta and fecha.

PBI.JPG

Thank you for your help.

Regards.

@Vvelarde @Ashish_Mathur 

4 REPLIES 4
Super User
Super User

Re: How to create colum with max values with conditions

Hi @rmcneish,

Edited

Try this for a calculated column in the table you show (Table1):

NewColMaxSales =
CALCULATE ( MAX ( Table1[Sales] ), ALLEXCEPT ( Table1, Table1[PLANTA], Table1[FECHA P.], Table1[TURNO] ) )

 

Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and run a quick test, plus it increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

 

rmcneish Regular Visitor
Regular Visitor

Re: How to create colum with max values with conditions

Hi @AlB .

This table only is a litle sample from my big table with 47 fields.

rmcneish Regular Visitor
Regular Visitor

Re: How to create colum with max values with conditions

@AlB 

I try that, but not found yet, throws me the value of the whole table.

I upload my pbix, and the column that i am creating is "MAX HOUR" from table ZQM_CNTL_P_DIARI.

MAX HOUR = CALCULATE(MAX(ZQM_CNTL_P_DIARI[HORA]),ALLEXCEPT(ZQM_CNTL_P_DIARI,ZQM_CNTL_P_DIARI[PLANTA],ZQM_CNTL_P_DIARI[TURNO],ZQM_CNTL_P_DIARI[FECHA P.]))

http://bit.do/eNpJ4

 

Super User
Super User

Re: How to create colum with max values with conditions

The measure appears to be working fine.  I see it return the correct values. 

The issue you might have is that [HORA] is taking values above 24 hours and that makes it roll over to the next day. Look for instance for the value 31/12/1899 07:00:00 in [HORA] if you see it as DateTime. Values under 24 hours should remain in 30/12/1899, which is the 0 in DAX DateTime type.  So you might have to check that out. 

I would also turn of the option Auto DateTime. It can cause unexpected behavior. Check this posting for more info 

 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 174 members 2,273 guests
Please welcome our newest community members: