Forum Discussion

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

Tips and Tricks

Resolver I

8 years ago

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 :smileyhappy:

Saaharjit
Helper I
8 years ago
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
  Microsoft Employee
  8 years ago
  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
    Helper I
    8 years ago
    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.

Related content