11 Replies Latest reply: Oct 18, 2017 5:20 AM by pradosh thakur RSS

    How to define background color of auto-sum row in Pivot Table?

    Ivan Yang

      Dear QS Experts


      I am new to QS and I have a question about color by expression

      I have 2 sheets (‘Sample’ & ‘Target’) and linked via ‘Region’ column in QS as below

      01.PNG


       

      Then I create a simple pivot table with auto-sum row and set background color with expression. The measurement is Make% ---- Count({<[Met]={'1'}>}[Order Line])/Count([Order Line]

      01.PNG

       

      If Make% meet the target in ‘Target’ sheet then it shows green otherwise green. Color expression as below

      01.PNG

       

      There is no sum target in the target sheet and I am not allowed to change raw data

      But sum target calculation method is as below:

      Q2:  ((HK(0.63)+TH(0.65)+INDO(0.72)+JAPAN(0.63))/4=0.65

      Q3:  ((HK(0.65)+TH(0.67)+INDO(0.75)+JAPAN(0.65))/4=0.68


      All the rows work well except the ‘Auto-Sum’ row. As you can see 61% is lower than above sum Q2 & Q3 target but it still shows green color

       

      May I know how to adjust my expression to make it right?

      You could check attached raw data and apps for your reference

       

      Thanks for your advice in advance

      Ivan