How to compare two columns

[Saaharjit]

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 the values of the Sales_price column is lesser than the total of Total_value column then print the Sales_price value in the 3rd column. If Sales_price is higher than the total of Total_value then print total of total value column on the 3rd column.

For eg:- 

in the image below.

Sales_price = 2,303.08

Sum(Total_value) = 13,801

Actual Price(3rd column) = 2,303.08

[BalaVenuGopal]

Hi @Saaharjit ,

You can do this by two ways either create calculated column in table adn define your logic there

or create new measure which will use your existing 2 measurs to derive third measure.

Plesae mark as solution if this works for you !!!!!!!!!!!!!!!!!!!

Thank you 

[Saaharjit]

@BalaVenuGopal 

This is what i did.  

MAXSALESVALUE = MAX(Query1[Sales_price])

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

WIPMAX(3rd column) = WIPMAX = IF([SumOfTotalValue] > [MAXSALESVALUE],[MAXSALESVALUE],[SumOfTotalValue])

I am getting the correct values but for some reason the grand total is wrong. It should be somewhere close to 3 million

 

[v-huizhn-msft]

Hi @Saaharjit,

Please try the following formula and check if it works fine.

WIPMAX =
SUMX (
    Query1,
    IF ( [SumOfTotalValue] > [MAXSALESVALUE], [MAXSALESVALUE], [SumOfTotalValue] )
)

This is a article which explains why and how to resolve similar issue:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Best Regards,
Angelia

[Saaharjit]

Hi @v-huizhn-msft,

Thanks for your reply. I tried your query but its still wrong.

WIPMAX12 is what i get from your query. WIPMAX is what i get from my query, which is almost correct except the grand total is wrong.

I want to campare the subtotal  of each group and then generate WIPMAX. 

[v-huizhn-msft]

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]

@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.

[Saaharjit]

@v-huizhn-msft Any idea where i am going wrong