Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
alexw94
Helper I
Helper I

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

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.

 

New colum creation based on max.png

1 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_0-1686306298050.png

 

View solution in original post

10 REPLIES 10
alexw94
Helper I
Helper I

@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. 

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.

vrzhoumsft_0-1686644075074.png

 

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.

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.

@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.
Ritaf1983_0-1686311573722.png

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

 

@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
Helper I
Helper I

@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?

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

@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?

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_0-1686306298050.png

 

Ritaf1983
Super User
Super User

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")
Ritaf1983_1-1686104995312.png

 

Ritaf1983_0-1686104974982.png

 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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors