Forum Discussion

Saaharjit's avatar
Saaharjit
Helper I
8 years ago

How to compare two columns

I have two columns in my matrix, Sales_price and Total_value. I want to compare the two columns and generate a 3rd column. The 3rd column will have values based on the comparison on the 2 columns. If...
Need Help
Show and Tell
Tips and Tricks
v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
8 years ago

Hi Saaharjit,

I create the following sample table for test because I don't have your resource table.



Create measures using the formula. And get expected result as the screenshot shown.

Max = MAX(Data[Fact])

sumOfTotalValue = SUM(Data[Fact])

WIPMAX = IF([sumOfTotalValue]>[Max],[Max],[sumOfTotalValue])

WIPMAX_test = SUMX(FILTER(VALUES(Data[ref_Employee]),[sumOfTotalValue]>[Max]),[Max])+0



So in your scenario, please create the measure using the formula.

WIPMAX_T =
SUMX (
    FILTER ( VALUES ( Query1[WOROOT] ), [SumOfTotalValue] > [MAXSALESVALUE] ),
    [MAXSALESVALUE]
)
    + 0


Best Regards,
Angelia

Saaharjit's avatar
Saaharjit
Helper I
8 years ago

v-huizhn-msft 

 

I tried your formula but now when the Total values is more than sales value its adding the 2 values together.

 

Here are my formulas

MAXSALESVALUE = MAX(Query1[Sales_price])

 

SumOfTotalValue = CALCULATE(SUM(Query1[Total_value]),ALLSELECTED(Query1[WORK_ORDER_NUMBER]) )

 

WIPMAX_T = SUMX (FILTER ( VALUES ( Query1[WOROOT] ), [SumOfTotalValue] > [MAXSALESVALUE] ),[MAXSALESVALUE])+SumOfTotalValue]

 

when the total value is less than sales price then it is fine.