Using MAX in an IF statement

[Anonymous]

Hi all,

Im very new to this, this is my first post.

I am having some issues with an IF statement where I am trying to find the MAX value from 5 different columns, where the column is the MAX I want it to return different text. Most of the outputs from my new column  are correct but some of them are not and I cannot figure out why as it works on most?

Here is my formula.

1 Strategic Priority 2 = if('pbi_table'[Strategic Score]=0,"Not Assigned",

2 if(MAX(pbi_table[Closer Score],MAX(pbi_table[Growth Score],MAX(pbi_table[Proud Score],MAX(pbi_table[£ Score],MAX(pbi_table[Transform Score])))))=pbi_table[Closer Score],"Closer",

3 if(MAX(pbi_table[Closer Score],MAX(pbi_table[Growth Score],MAX(pbi_table[Proud Score],MAX(pbi_table[£ Score],MAX(pbi_table[Transform Score])))))=pbi_table[Growth Score],"Growth",

4 if(MAX(pbi_table[Closer Score],MAX(pbi_table[Growth Score],MAX(pbi_table[Proud Score],MAX(pbi_table[£ Score],MAX(pbi_table[Transform Score])))))=pbi_table[Proud Score],"Proud",

5 if(MAX(pbi_table[Closer Score],MAX(pbi_table[Growth Score],MAX(pbi_table[Proud Score],MAX(pbi_table[£ Score],MAX(pbi_table[Transform Score])))))=pbi_table[£ Score],"Cap",

6 if(MAX(pbi_table[Closer Score],MAX(pbi_table[Growth Score],MAX(pbi_table[Proud Score],MAX(pbi_table[£ Score],MAX(pbi_table[Transform Score])))))=pbi_table[Transform Score],"Transform",

7 "Investigate"))))))

Any help would be much appreciated.

[vivran22]

Hello

Welcome to the Power BI Community.

Is it possible to share the sample data along with the desired output? The requirement is not very clear from your current post.

Thanks!

Vivek

[Anonymous]

I cant see how to attach an excel file so I have added a sample in th etable below. Columns B-F are provided a score. Column G (Top Score) is a MAX calculation of Range B-F. Then Priority is an IF statement using MAX. It is very easy to do this in excel but I am trying to recreate this in power BI and I do not obtain the same results.

There is another slight issue in that on occassion the MAX number is matched between columns, then I need to figure out what to do in that scenario.

Title	£ Score	Trans Score	Closer Score	Proud score	growth score	Top score	Priority
1	15	0	8	0	0	15	Top Score
2	15	20	0	0	0	20	Trans Score
3	0	0	5	20	0	20	Proud Score
4	20	15	0	0	0	20	£ Score
5	0	0	0	0	0	0	Not Assigned