How to calculate depend on accurate text

[Anonymous]

Hello Community,

I'm trying to create measure which will calculate (column A/column B) depend on value in column C (text).

The goal is to get one formula which is able to recognaze if customize contition will check value from column C and base on true/false will choose one from two options of calculation:

Score = (column A/column B) if row in column C is <> "text"

Score = (column A/column B)/2 if row in column C is = "text".

Finally i tried to create on measure like:

EFEKT. = if('efektywność'[proces] = "wtryskarki", sum('Efektywność'[Ilość godzin SAP (PIM1)])/sum('Efektywność'[Ilość rbh (PIM1)])/2, sum('Efektywność'[Ilość godzin SAP (PIM1)])/sum('Efektywność'[Ilość rbh (PIM1)]))

The error message is telling that program cannot define one value in column "proces" in table 'efektywność'.

I'm aware that there is lack of agregation form like sum/min/max before 'efektywność'[process] expression but finally i don't want to aggregate data from column "proces". I just want to check if it's = or <> from an assumption and next choose righ one calculation (without or with "/2" at the end. 

Does anybody know how to create this type of measure? Thanks in advance for any suggestions 🙂 

Regards,

Jakub

[Anonymous]

Aggregate form "max" is working for my case. Thank you @amitchandak ! :).

I was not aware that "max" can be use for text values..

Best Regards,

Jakub

[amitchandak · Accepted solution]

@Anonymous , measure can take measure or aggregated columns

EFEKT. = if(max('efektywność'[process])  = "wtryskarki", sum('Efektywność'[Ilość godzin SAP (PIM1)])/sum('Efektywność'[Ilość rbh (PIM1)])/2, sum('Efektywność'[Ilość godzin SAP (PIM1)])/sum('Efektywność'[Ilość rbh (PIM1)]))

or

use filter

EFEKT. = calculate( sum('Efektywność'[Ilość godzin SAP (PIM1)])/sum('Efektywność'[Ilość rbh (PIM1)])/2, sum('Efektywność'[Ilość godzin SAP (PIM1)])/sum('Efektywność'[Ilość rbh (PIM1)]) , filter( 'efektywność' ,'efektywność'[process] = "wtryskarki" ))