Forum Discussion
Extract Maximum number against same row from data in multiple columns
I have a dataset wherein i want to calculate maximum amount from multiple columns against a single day/product/etc. Result in last column below;
| ProductID | Salary | Income | Other Income | Gross Profit | Present Salary | RESULT (MAX Value Needed) |
| ABCD | 1000 | 1800 | 0 | 7500 | 2100 | 7500 |
| DEFG | 200 | 7500 | NUL | 1100 | 955 | 7500 |
| HIJK | 6000 | 12000 | 850 | 325 | 12000 | |
| KLKM | 500 | 9700 | 5000 | 9750 | 8850 | 9750 |
pls try this
the first option is if you have measures in your columns
RESULT= MAXX ( { [Salary],[Income],[Other Income],[Gross Profit],[Present Salary]},[Value]) ------ RESULT= VAR _t1 = CALCULATE(SUM('yourtable'[Salary])) VAR _t2 = CALCULATE(SUM('yourtable'[Income])) VAR _t3 = CALCULATE(SUM('yourtable'[Other Income])) VAR _t4 = CALCULATE(SUM('yourtable'[Gross Profit])) VAR _t5 = CALCULATE(SUM('yourtable'[Present Salary])) RETURN MAXX ( { _t1,_t2,_t3,_t4,_t5},[Value])
3 Replies
- Ahmedx
Super User
pls try this
the first option is if you have measures in your columns
RESULT= MAXX ( { [Salary],[Income],[Other Income],[Gross Profit],[Present Salary]},[Value]) ------ RESULT= VAR _t1 = CALCULATE(SUM('yourtable'[Salary])) VAR _t2 = CALCULATE(SUM('yourtable'[Income])) VAR _t3 = CALCULATE(SUM('yourtable'[Other Income])) VAR _t4 = CALCULATE(SUM('yourtable'[Gross Profit])) VAR _t5 = CALCULATE(SUM('yourtable'[Present Salary])) RETURN MAXX ( { _t1,_t2,_t3,_t4,_t5},[Value])Ahmedx Thanks for the reply it worked very well, however if I use same formula to extract MAX out of some other numeric data columns then it shows below message
"A circular dependency was detected: DC061123[DR_LIMIT], DC061123[Inflow], DC061123[DR_LIMIT]."
Formula Used
RESULT1=
VAR _t1 = CALCULATE(SUM('yourtable'[DR_Turnover]))
VAR _t2 = CALCULATE(SUM('yourtable'[DR_Tunover1]))RETURN
MAXX (
{ _t1,_t2},[Value])kindly guide
- Ahmedx
pls share the file so we can help you
