The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to Solution.
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])
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
pls share the file so we can help you