How to create a column in a power bi table that will show the maxium value of some data each month?

[alexw94]

So I some data in a power bi that comes from an outside datasource (not excel) so dont have the option to edit in power query becuase it is protected by specific people in my organisation.

I need to create a colum where the top value is identified per month. The value in the "Score" column is a measure, please can someone help as to what formula i need to write in my new measure/column that will achieve the result. I have simulated how I want the final table to look in a screenshot below which was done in excel.

Top for the month is what I want to create and this will show the highest value, once I have got this I intend to filter the table to "Yes" only so it only shows the top scoring events for each month, please can someone help me achieve this?

[mahenkj2]

Hi @alexw94 

You can not edit the data in PQ, but may still transform and then load. 

Meanwhile, your intended goal can also be achied with a DAX formula as well:

Top for the month = 
CALCULATE(MAX(Tops[Score]),REMOVEFILTERS(Tops[Event],Tops[Score]))

Top(Y/N) = if(Tops[Top for the month]=SUM(Tops[Score]),"Yes","No")

Above is just an idea, you can tweak as per your actual data and needs.

Hope it helps.