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

    Highlight Min values by row

    keerthana kumar

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

        • 1. Re: Highlight Min values by row
          Michel Caissie

          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
            keerthana kumar

            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.

             

             

            minimum total.png

            • 3. Re: Highlight Min values by row
              Michel Caissie

              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
                keerthana kumar

                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
                  Michel Caissie

                  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