5 Replies Latest reply on Oct 3, 2018 3:07 PM by Michel Caissie

# Highlight Min values by row

In this superstore dashboard I want to highlight the min(sales) happened between September[CURRENT MONTH] and December[CURRENT MONTH +3].

• ###### 1. Re: Highlight Min values by row

Keerthana,

Is this what you are looking for ?

I get the current month + 3 with

DATEDIFF('month', DATE(DATENAME('year',TODAY()) +"-" + DATENAME('month',[Order Date])+ "-1"),TODAY() ) <= 0

and

DATEDIFF('month', DATE(DATENAME('year',TODAY()) +"-" + DATENAME('month',[Order Date])+ "-1"),TODAY() )

The following returns true  if the sales is the min of those 4 months

SUM( Sales ) =

MIN({EXCLUDE DATEPART('month', [Order Date]):

MIN({INCLUDE [Category], DATEPART('year', [Order Date]),DATEPART('month', [Order Date]):SUM(if [isSeptToDec] then Sales end)})

})

The following calculation is for the color shelf,  allowing to set a different color to the min values while keeping the diverging blues for the other values

if [IsSalesMin] then  -100000 else SUM( Sales ) end

This will mess up your color legend, but you can always bring a hidden copy of the original worksheet in the Dashboard and show that color legend instead.

Michel

1 of 1 people found this helpful
• ###### 2. Re: Highlight Min values by row

Thanks Michel !!

I looking for the same . I understood the concept . Now when im creating a data tab i have a small issue ,below highlighted

I created a separate column Current month sales(sept), minimum in the range sept-dec.

Now Column Grand Total and Sub Total also gives me the min , I want a sum of all there . How can we achieve it.

• ###### 3. Re: Highlight Min values by row

Keerthana,

You can replace your calculation with

if first() = last() then

SUM({EXCLUDE DATEPART('month', [Order Date]):

MIN({INCLUDE [Category], DATEPART('month', [Order Date]):SUM(if [isSeptToDec] then Sales end)})

})

else

MIN({EXCLUDE DATEPART('month', [Order Date]):

MIN({INCLUDE [Category], DATEPART('month', [Order Date]):SUM(if [isSeptToDec] then Sales end)})

})

end

computing using  sub-category

as you can see on Sheet 3 ,  first will equal last only for the Total rows,  in which case you do a SUM,  else you do a MIN.

Michel

2 of 2 people found this helpful
• ###### 4. Re: Highlight Min values by row

Thanks for the help Michel Caissie.

Its solved my problem . Just one last question

Sub categ Totals to appear if I add All Subtotals and drill down from SubCateg - Product .

• ###### 5. Re: Highlight Min values by row

Keerthana,

I am quite busy these days, and your last requests brings some complexity.

First thing, for the previous question  I realize that you could have put only

MIN({EXCLUDE DATEPART('month', [Order Date]):

MIN({INCLUDE DATEPART('month', [Order Date]):SUM(if [isSeptToDec] then Sales end)})

})

then right-click the calculation green-pill and select Total using  SUM instead of Automatic.

The if first() = last()  is an old trick  before there were the  "Total using" features,  and I still have the reflex to use it.

Next, when you drill down to the product, how do you want to consider products that don't have data in the 4 months. Do you want the min to be 0 or you want the min of the months having a value. If you want it to be 0  you would need to compute the number of months a product had sales. Something like

{FIXED [Category], [Sub-Category], [Product Name]: COUNTD( if [isSeptToDec] then DATEPART('month', [Order Date]) end )}

Next you would need to adjust you min calculation,  something like

MIN({EXCLUDE DATEPART('month', [Order Date]):

MIN({INCLUDE DATEPART('month', [Order Date]):SUM(if [isSeptToDec] and [nbMonthWithSales] = 4 then  Sales else 0 end) })

})

But this calc would work only when the hierarchy is fully exploded.  You would need a different calc with different  lod  when you show only Category , or Sub-Category.

And it is not easy to apply a different calc when the  hierarchy level changes.

If you have a fixed number of elements in each dimension  you can always use the size()  function. Depending on the result you know if you show only Category, or also  Sub-Category  or  also Product Name.  But then you add a table calculation in your Min calc, with the side effect that you cannot use  Total using SUM  anymore.  And you cannot use if first() = last()  because  you would need the computing to change dynamically depending on the level of the hierarchy in the view.

I am not saying that it's not possible  but I don't have the bandwidth at this time to  dig it further.

Michel

1 of 1 people found this helpful