Display Column name of Max value from list of columns

[binayjethwa]

Hi , I have the following scenario , where 

I have Sales1, Sales 2,Sales 3, Sales 4, Sales 5 Columns in my table , now i have another column sales % which will give my % sales , so if Sales % is between 1 and 50 percent , i should check for all the columns from sales 1 to sales 5 and return column name which has maximum value . Check below table for reference. 

I am looking to create  result column , for example for Emp Binay sales % is 5% so i will have to check all columns from sales 1 to sales 5 and return the column name for which it has higher values , in this case its sales 3 since it has 19 

similary for Binay 3 , max value from columns sales 1 to 5 in that row is 33 , so the result will be sales 5.

Note:  This is a sample table , i have other columns also in my table. But i am specifically looking to build Result column.

Please help with this scenario.

EMP name	Emp No	Sales 1	Sales 2	Sales 3	Sales 4	Sales 5	Sales %	Result
Binay	1223	0	0	19	0	0	5%	Sales 3
Binay1	1224	0	33	19	0	0	7%	Sales 2
Binay2	1225	11	22	19	1	0	49%	Sales 2
Binay3	1226	2	3	19	0	33	11%	Sales 5
Binay4	1227	0	1	19	44	0	22%	Sales 4

Thanks,

Binay

[binayjethwa]

Hi @Ahmedx , 

 I have used the above as a measure and i see below table , i am pulling all my sales values to tables and it gives me sum of sales 1 , sum of sales 2 etc and i am not doing dont summarize here , and now i have other column sales % for which if it is between 1% to 50 % then the  measure condition should apply. But i did the same and i am seeing below values .

I have created the measure as below. 

Max Columnname_Measure=

VAR t1 = sum ('Table'[Sales 1])
VAR t2 = sum ('Table'[Sales 2])
VAR t3 = sum ('Table'[Sales 3])
VAR t4 = sum ('Table'[Sales 4])
VAR t5 = sum ('Table'[Sales 5])
VAR _maxDate =MAXX({[t1],[t2],[t3],[t4],[t5]},[Value])
RETURN
IF(sales%)>=0.01 && sales% <=0.05,
SWITCH(_maxDate,
t1,"Sales 1",
t2,"Sales 2",
t3,"Sales 3",
t4,"Sales 4",
t5,"Sales 5"
),blank())

Here the highlighted values with % between 1 and 50% although the values are there i am seeing blank. 

1. If all values are blank i am seeing the 

[Ahmedx · Accepted solution]

[sales%] this measure?
try this

Max Columnname_Measure=

VAR t1 = sum ('Table'[Sales 1])
VAR t2 = sum ('Table'[Sales 2])
VAR t3 = sum ('Table'[Sales 3])
VAR t4 = sum ('Table'[Sales 4])
VAR t5 = sum ('Table'[Sales 5])
VAR _maxDate =MAXX({[t1],[t2],[t3],[t4],[t5]},[Value])
RETURN
IF(
[sales%] >=0.01&& [sales%] <=0.05,
SWITCH(_maxDate,
t1,"Sales 1",
t2,"Sales 2",
t3,"Sales 3",
t4,"Sales 4",
t5,"Sales 5"
))

[Ahmedx]

Is this what you are looking for?

Column = 
 VAR _EMP = [EMP name]
VAR _tbl = FILTER(ALL('Table'),'Table'[EMP name]=_EMP)
VAR t1 = MAXX(_tbl,[Sales 1])
VAR t2 = MAXX(_tbl,[Sales 2])
VAR t3 = MAXX(_tbl,[Sales 3])
VAR t4 = MAXX(_tbl,[Sales 4])
VAR t5 = MAXX(_tbl,[Sales 5])
VAR _maxDate = MAXX({t1,t2,t3,t4,t5},[Value])
RETURN 
SWITCH(_maxDate,
        t1,"Sales 1",
        t2,"Sales 2",
        t3,"Sales 3",
        t4,"Sales 4",
        t5,"Sales 5"
)

[binayjethwa]

Hi @Ahmedx , I have tried the dax shared but since the data is summarized in the table i am seeing many rows for single employee. Because one person can have multiple rows in table. And my sales is Sum(sales) which i aggregated in table visual. currently i see result as many rows in my data .  But it should be ony one row.

Thanks,

Binay

[Ahmedx]

well, you need to aggregate them in a matrix and write such a measure

t1 = sum ('Table'[Sales 1])
t2 = sum ('Table'[Sales 2])
t3 = sum ('Table'[Sales 3])
t4 = sum ('Table'[Sales 4])
t5 = sum ('Table'[Sales 5])
--------------------------
Measure = 
VAR _maxDate =MAXX({[t1],[t2],[t3],[t4],[t5]},[Value])
RETURN
SWITCH(_maxDate,
        t1,"Sales 1",
        t2,"Sales 2",
        t3,"Sales 3",
        t4,"Sales 4",
        t5,"Sales 5"
)

 could you share your pbix-file? Or create an example file which reproduces your issue?

[Ahmedx]

or

Column = 
VAR t1 = [Sales 1]
VAR t2 = [Sales 2]
VAR t3 = [Sales 3]
VAR t4 = [Sales 4]
VAR t5 = [Sales 5]
VAR _maxDate =MAXX({t1,t2,t3,t4,t5},[Value])
RETURN
SWITCH(_maxDate,
        t1,"Sales 1",
        t2,"Sales 2",
        t3,"Sales 3",
        t4,"Sales 4",
        t5,"Sales 5"
)

[Ahmedx]

pls try this

Column = 
VAR t1 = CALCULATE(MAX('Table'[Sales 1]))
VAR t2 = CALCULATE(MAX('Table'[Sales 2]))
VAR t3 = CALCULATE(MAX('Table'[Sales 3]))
VAR t4 = CALCULATE(MAX('Table'[Sales 4]))
VAR t5 = CALCULATE(MAX('Table'[Sales 5]))
VAR _maxDate =MAXX({t1,t2,t3,t4,t5},[Value])
RETURN
SWITCH(_maxDate,
        t1,"Sales 1",
        t2,"Sales 2",
        t3,"Sales 3",
        t4,"Sales 4",
        t5,"Sales 5"
)