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:

Helper I

8 years ago

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

Related content