7 Replies Latest reply on Feb 6, 2019 8:27 AM by Shinichiro Murakami

# Display the Dimension value for maximum measure value

I have a larg(ish) table (Roughly 2,400,000 rows) of hourly measurements of NO2 (Nitrogen Dioxide) from 99 different stations (locations where NO2 is measured).  I need to display;

• The average, yearly station average (i.e. average the hourly measurements for each station and then average all of the stations)
• The maximum station average for each year
• The minimum yearly station average for each year

I want to display the name of the station as a label for each year for the maximum and minimum values.  This is where I'm stuck.

I've got the average yearly station average with this calculated field {INCLUDE [Station Id] : AVG([Hourly NO2 ppb])}

I've got the max station average with this calculated MAX([Station Average NO2 ppb])

I've got the min station average with this calculation Min([Station Average NO2 ppb])

I'm displaying the three measures in a vis like this;

I don't know how to write a formula which will derive the station name for the max and min value for each year.  Can someone help with this?

I've tried IF MAX({ INCLUDE [Station Id] : AVG([Hourly NO2 ppb])}) = [Max Station Average NO2 ppb] THEN ATTR([Name]) END.  When I drag that on to the label tile asterisks are displayed at each data point.

I Found this Find MAX(value) at different levels of detail, return string where category is max which got me a little closer when I put the 2nd calculated field as a Tooltip (it puts the same i.e. station name of the max average station on all data points).

• ###### 1. Re: Display the Dimension value for maximum measure value

Hi, Michael

Can you provide a sample workbook?

ZZ

• ###### 2. Re: Display the Dimension value for maximum measure value

Put Label kin text as attributes.

Only shows Max of Measure values, which limits only "MAX" label.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 3. Re: Display the Dimension value for maximum measure value

This is my first packaged workbook posted on a forum.  I hope I've done it right.  This is created with Version 10.1

• ###### 4. Re: Display the Dimension value for maximum measure value

Hi Michael,

Best,

Paul

• ###### 5. Re: Display the Dimension value for maximum measure value

Shin;

Thanks so much for your response!  I was following along until I got to the formulas for Min Category and Max Category.  For me, they were created as measures, with the text "(Count)" appended to them, however, once I dragged them onto the Label tile as attr they worked as you've illustrated above.

I am confused by how you were able to display labels for only the max line and not for the average line.  I was able to hide them by setting the mark label to never show, which is ok for the visualization but the wrong Station Name is displayed for the tooltip.

I've attached an updated workbook.  See sheet 4

Also, this vis only works when the date dimension is set to year (because all of the calculations use the FIXED LoD.  Would these these calculations work if I changed them to INCLUDE?

• ###### 6. Re: Display the Dimension value for maximum measure value

Paul;

Thanks for the response.  I've gotten most of the way but have some questions.  See the post above.

• ###### 7. Re: Display the Dimension value for maximum measure value

Thank you to all who supported me through this problem.  For the sake of completeness I'm attaching my final solution.

Ultimately I landed on a hybrid of the solution provided by Shin.  The variation I chose enabled more control over the mark labels and tool tip.  I used INCLUDE LoD's instead of fixed so that I can easily show more date granularity.

Shin.  Your solution, in particular the Min and Max category formulas, was the key to getting me unstuck.  Thank you so much.

• ###### 8. Re: Display the Dimension value for maximum measure value

You are welcome.