Show Column A value that matches particular condition in Column B

[anond]

I have a database where column A is the name of the salesman and column B is the products they sold in January. Column C is the products they sold in Feb and so on, 

Salesman____Jan___Feb___Mar

A_____________100__101___98

B______________109__98____103

C_____________101___102___104

I want to create a new table that shows the names of the salesmen who sold the max in each of the months. So in the above case, it will look something like this:

Jan____B

Feb____C

Mar____C

Is there a way to do this? I tried creating a new measure that picks the MAX(Jan), MAX (Feb), etc, but that only picks the value. How do I pick the element from Column A corresponding to that?

Thanks all. 

[Datatouille]

Hi @anond

In case you need this on a matrix visual (not on an other table that you would load physically):

First, go to the Query Editor and unpivot your data.

Right click on Salesman column and "unpivot all the other columns".

Close & apply the steps of the query Editor.

Second, create the following measure:

Sales = Sum(YourTable[SalesColumn]

Third, compute who is the best performer:

Best Performer = Summarize ( TopN(1, YourTable, [Sales], DESC), YourTable[Salesmancolumn] )

Chose a matrix visual and put the month column as an axis, the "best performer" measure as a value.

[anond]

Thank you so much for the help @Datatouille. I am a newbie (literally started working on Power BI today). So appreciate all your help here. I am yet to sit down and try to understand why we are doing each of these things. But I was not successful with the last step. 

This is how I have it for the Best Performer measure: https://imgur.com/a/VFOPK

Should we use TABLE in place of 'YourTable' in your post?
Also, I am not being prompted for Sheet1[SDR] for some reason (that's the Salesman field in your post). 

What am I doing wrong here? Thank you once again. 

[Datatouille]

Ok so you need a proper table ?

My solution was actually a measure.

Try this in a measure and adapt it to your example !

[anond]

No, I don't need it as a column (or a measure) specifically. Just learning to do things at the moment. 

I did use 'Best Performer' as a measure. But it is throwing the error that you see in the screenshot. 

[vanessafvg]

@anond would this not be a good case for unpivoting in power query?

http://chandoo.org/wp/2015/09/29/unpivot-data-with-power-query/

[anond]

@vanessafvg Thanks for the link. But that only gives the tutorial for unpivoting which I was able to do on Power BI as well. I am trying to learn a way to find the highest seller for each month using Power BI.

[v-chuncz-msft]

@anond,

You may add a new table by DAX below.

Table =
FILTER (
    Sheet1,
    RANKX (
        CALCULATETABLE ( Sheet1, ALLEXCEPT ( Sheet1, Sheet1[Attribute] ) ),
        Sheet1[Value],
        ,
        DESC,
        DENSE
    )
        <= 1
)

[anond]

@v-chuncz-msft

Thank you for your comment. I believe I would need to unpivot before creating this new table, right? I tried it and get an error that reads 'The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value'. Any pointers here? 

[v-chuncz-msft · Accepted solution]

@anond,

Yes. I mean the calculated table, not measure.

https://powerbi.microsoft.com/en-us/guided-learning/powerbi-learning-2-6-create-calculated-tables/

[vanessafvg]

@anond oops sorry wasn't reading it properly!

[Datatouille]

I also have a solution using Power Query

Salesman   Jan       Feb      March

A	50	100	20
B	10	30	80
C	100	80	10

This Table is called 'Data"

Power Query

Open the Query Editor and in the 'Data' Query 

Unpivot all the months, rename the appropriate columns and make sure your data type is OK.

This corresponds to the following M Code:

let
  Source = "YourSource"
  Unpiv = Table.UnpivotOtherColumns(Source, {"Salesman"}, "Month", "Amount"),
  ModifType = Table.TransformColumnTypes(Unpiv,{{"Amount", Int64.Type}})
in
  ModifType

Then, launch a new blank query with the following code:

let
 Source = Table.Group(Data, {"Month"}, {{"Best", each List.Max([Amount]), Int64.Type}}),
 DoubleMerge = Table.NestedJoin(Source,{"Month", "Best"},Data,{"Month", "Amount"},"Data",JoinKind.LeftOuter),
 Expand = Table.ExpandTableColumn(DoubleMerge, "Data", {"Salesman"}, {"Salesman"})
in
 Expand

In this solution, we are referencing the previous query 'Data' by grouping (by month) & getting the best salesman.