How to find Min and Max Values by Group

[leale1997]

I would like to find the Min and Max value of a column based on the grouping it belongs to.  Grouping is designated by a combination of the values in two columns.

The data looks like this:

Category           Sub Category          Days

A                            1                                   10

A                            1                                    20

A                             1                                   30

So for Category A,1 the Min is 10 and the Max is 30

I attached a data sample for you.

Thanks in advance for any assistance :0)

[dplr-rn]

explain what you are trying to achive in more detail. because context matters.

e.g. if you are doing on a chart its simple as adding category and sub category as dimension and give measures min and max

if you want it in someway in the data model you will need to resident load and group by category and sub category

[leale1997]

Good question.  As I give it more thought I actually have three groupings or columns to group by. 

Category           Sub Category1        Sub Category2          Days

A                            1                                   a                                      10

A                            1                                   b                                      20

A                             1                                  b                                      30

A                              1                                  a                                      5

My initial thoughts were to incorporate a chart or straight table that might look something like this:

Product   -     Range

A,1,a         -     5 - 10

A,1,b         -      20 - 30

This would require the 'grouping' with Min&Max to only be in the chart or graph.

Now that you have me thinking about it more.  It might be easier to include the grouping in the data model?  It might also allow me more flexibility with buttons for drill downs and other analytics.  

Can I add it to the data model?

[dplr-rn]

you could add it to data model but it may reduce flexibility.

i would say do it chart. 

unless there is a special scenario you need

[leale1997]

ok.  So how would I include the grouping with Min and Max values in a chart?  I attached a better data set for you that has he second category added in.  

Also note that what am initially trying to do is just group them in a table.  With 'Brand' and 'TA' being rows and Market being columns. Might actually be easier in a pivot table.

[dplr-rn]

like i mentioned in the initial reply. when you give aggregations in a measure (sum, min, max avg ) it is automatically grouped according to the dimensions given.

so in you case you give Brand, TA, Market as dimensions. a measure like Min(Days) will give you minimum grouped by those dimension values. similary max(Days) will give you maximum groups by those dimensions