How to compare two columns

Question

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 · Answer

Hi&nbsp;Saaharjit&nbsp;,&nbsp;You can do this by two ways either create calculated column in table adn define your logic thereor create new measure which will use your existing 2 measurs to derive third measure.&nbsp;&nbsp;Plesae mark as solution if this works for you !!!!!!!!!!!!!!!!!!!Thank you&nbsp;:smileyhappy:

saaharjit · Answer

BalaVenuGopal&nbsp;&nbsp;This is what i did.&nbsp;&nbsp;&nbsp;MAXSALESVALUE = MAX(Query1[Sales_price])&nbsp;SumOfTotalValue = CALCULATE(SUM(Query1[Total_value]),ALLSELECTED(Query1[WORK_ORDER_NUMBER]) )&nbsp;WIPMAX(3rd column) =&nbsp;WIPMAX = IF([SumOfTotalValue] &gt; [MAXSALESVALUE],[MAXSALESVALUE],[SumOfTotalValue])&nbsp;I am getting the correct values but for some reason the grand total is wrong. It should be somewhere close to 3 million&nbsp;&nbsp;

v-huizhn-msft · Answer

Hi&nbsp;Saaharjit,Please try the following formula and check if it works fine.
WIPMAX =
SUMX (
    Query1,
    IF ( [SumOfTotalValue] &gt; [MAXSALESVALUE], [MAXSALESVALUE], [SumOfTotalValue] )
)

&nbsp;
This is a article which&nbsp;explains why and how to resolve similar issue:
&nbsp;
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Best Regards,Angelia

saaharjit · Answer

Hi&nbsp;v-huizhn-msft,&nbsp;Thanks for your reply. I tried your query but its still wrong.&nbsp;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.&nbsp;I want to campare the subtotal&nbsp; of each group and then generate WIPMAX.&nbsp;&nbsp;

v-huizhn-msft · Answer

Hi&nbsp;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]&gt;[Max],[Max],[sumOfTotalValue])

WIPMAX_test = SUMX(FILTER(VALUES(Data[ref_Employee]),[sumOfTotalValue]&gt;[Max]),[Max])+0
So in your scenario, please create the measure using the formula.
WIPMAX_T =
SUMX (
    FILTER ( VALUES ( Query1[WOROOT] ), [SumOfTotalValue] &gt; [MAXSALESVALUE] ),
    [MAXSALESVALUE]
)
    + 0

Best Regards,Angelia

Forum Discussion

How to compare two columns

7 Replies

Related content

Compare Dynamic Columns in a Matrix

Compare two columns in different tables

Comparing columns error

Comparing two tables columns by columns

Table Sort , sort order , comparer