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.

The "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" and that will show the required data, the top event scores for each month. Thank you.

[alexw94]

@Ritaf1983 thank you, but becuase its come from a datalake, I cannot edit it. ITs protected meaning i cant go into the table views for it. 

[v-rzhou-msft]

Hi @alexw94 ,

I suggest you to try MAXX() function to calculate the max measure value.

Yes/No = 
VAR _MAXSCORE = MAXX(ALLEXCEPT('Table','Table'[Month]),[Score])
RETURN
IF([Score] = _MAXSCORE,"Yes","No")

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[alexw94]

Im sorry to say this has not worked, I am getting red error lines underneath the whole statement. This is coming from a  DirectQuery from a datalake thats come from a system, i cannot change the data its coming from.

[Ritaf1983]

@alexw94 
You can get it as a measure than use formula:

Top Measure =

VAR MaxScore = CALCULATE(MAX('Table'[Score]), ALLEXCEPT('Table', 'Table'[Month]))

RETURN

    IF(MAX('Table'[Score]) = MaxScore, "Yes", "No")

but measure cannot be a slicer, you can put it just as a filter.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[alexw94]

@Ritaf1983  - Not worked sadly, whenever I type in the apostrophe none of the tables show, even when typing in the correct table between the 'Table' and the column name  [Score] it cannot find it and it displays a red line error mark underneath the statement. 

[alexw94]

@Ritaf1983 - this doesnt seemed to have worked, looking at my data, the score column is actually not a measure its just a normal column of data that isnt being picked up in the first part of the formula? Can you help me rectify this please?

[Ritaf1983]

Hi @alexw94 
Did you download a sample file that I linked?

I have 0 measures there too...only columns
Try downloading and following my steps and if it doesn't work.
If not

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[alexw94]

@Ritaf1983 - yes downloaded your file step by step followed, but has not worked. In the first part of your formula "Top? = if('Table'[Score]" - it is not picking up the "Score" column, or any column to that matter, just measures only. The error message I am getting is:

 " A single value for column 'Score" in table 'Table' cannot be determined. This can happen when a measure formula refers to a cololn that contains many values without specificying an aggregation such as min, max, count or sum to get a single result."

I cannot provide a sample data as this comes from meta data/data lake directly into my power bi desktop which is protected by my organisation. 

Alternatley, I  have created a measure with "Score" which is the same as score column, but it is not pointing out the top event. Instead it is return "Yes" for each entry, any ideas how i can sort this out please?

[Ritaf1983 · Accepted solution]

It seems you trying to create a measure instead of a calculated column.

Try to add a calculated column ...

Please refer the steps on picture 

[Ritaf1983]

Hi @alexw94

To achieve your goal and mar top month you can add a calculated column using Dax formula:

Top? = if('Table'[Score]=CALCULATE(max('Table'[Score]), ALLEXCEPT('Table','Table'[Month])),"Yes","No")

 Link to a sample file 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly