5 Replies Latest reply: Apr 20, 2012 9:46 AM by Daniel_C RSS

    Fix Table Values?

      Hi all,

       

      I got a specific problem with a table. As you can see in the picture below I got a table with some values on the dimension "Zeitzone". The values are all correct. The problem occurs, when I make a selection of the dimension (

       

      First.png

      The problem occurs, when I make a selection of the dimension (see below)

       

      Second.png

       

      The expression in the table for the "Abweichung" is this: =(Avg([Netto-Plan-ZZ])-(Sum([n/n/n])/Sum(TOTAL [n/n/n])))*100

       

      The problem is the red marked part. The value should only change, when I select a year, but not when I select any other dimension. Is it possible to do this?

      Or is there another way to fix the values in the table that it shows "0,2" for "13:00-17:00" and not "-85,3".

       

      I hope I made my problem clear to you and be glad for any help! If there are any questions, please ask.

       

      Thx

      Daniel

        • Fix Table Values?

          Hi,

          It would have been better had you attached the app. From what i understood you can try set analysis using this --

          Avg({<zeitzone=,year={$(=only(year)-1}>}[Netto-Plan-ZZ]).. Ididnt write the full expression as i am not getting the logic .

          • Fix Table Values?
            sivaraj seeman

            The problem is you are using total function, whenever you are selecting some dim it will show the same value for all

            try this

             

            Avg({$<Zeitzone>}[Netto-Plan-ZZ])-(Sum([n/n/n])/Sum(TOTAL [n/n/n])))*100 ------it will exclude the selection

            • Re: Fix Table Values?
              Celambarasan Adhimulam

              Hi,

              Check something like this

               

              =(Avg([Netto-Plan-ZZ])-(Sum([n/n/n])/Sum(TOTAL {1} [n/n/n])))*100

               

              Celambarasan

              • Fix Table Values?
                Jason Michaelides

                The TOTAL qualifier ignores the current row (dimension value) but is still based on the available dimension values, so if you select a dimension value (in this case 13:00-17:00) then you are limiting the available dimension values in the chart and TOTAL will adjust accordingly. You need to also exclude the dimension selection.

                 

                Instead of TOTAL, try {<Zeitzone=>} TOTAL giving:

                 

                =(Avg([Netto-Plan-ZZ])-(Sum([n/n/n])/Sum({<Zeitzone=>}TOTAL [n/n/n])))*100

                 

                 

                Hope this helps,

                 

                Jason