9 Replies Latest reply on Feb 23, 2018 4:46 AM by Tim Dines

    Dynamic Formatting

    gareth.brunsden.0

      I have read on the forums that there is no way to have dynamic/custom formatting, but this is something that would really benefit my dashboard.

      Is anyone able to help me with implementing something like:

      if [Revenue] < 1000 then [Revenue]

      elseif [Revenue] >= 1000 and [Revenue] < 1000000 then left([Revenue],2) + 'k'

      elseif [Revenue] >= 1000000 and [Revenue] < 1000000000 then left([Revenue],2) + 'm'

      elseif [Revenue] > 1000000000 then left([Revenue]) + 'b'

      else [Revenue]

      end

       

      Which would give me the below example results:

           

      ProductPriceQty SoldRevenueWhat I want
      Pencil10120                                  1,200 1k
      Scissors605                                      300 300
      Paper301600                              
        48,000
      48k
      Pen40250600                      
        10,024,000
      10m
      Paper clip35                                        15 15
      Printer6000170000                 
        1,020,000,000
      1b

       

      Please also find attached a 10.5 workbook to help illustrate my example.

        • 1. Re: Dynamic Formatting
          Abhilash Sharma

          Hi Gareth,

           

          I think what you have suggested can be implemented using a Calculated Field. All you have to do in the THEN clause is to convert this value to a String value so that you can append 'K' or 'B' etc. You can use the STR() function to achieve it.

           

          Also there is another way to show revenues but you will get to choose 1 option from the list. Please refer below screenshot:

           

          Please mark correct and/or helpful if it worked.

           

          -Abhilash

          1 of 2 people found this helpful
          • 2. Re: Dynamic Formatting
            YASIN CAN PARLAK

            Hi Gareth,

             

            I took a look at your workbook and figure it out like this! If this is the solution you are looking for, here is how you can do it;

            Firstly, to format your numbers as K(thousand), M(Million) , B(Billion) you can use custom formating! But before you do that, some calculated fields must be done. Here is the example for K and i think you can figure it out how to do other ones by following K example.

            After you create calculated fields for K,M and B ( can go on..), you can drag them to Text pane and format it !

             

             

            Here is the final screenshot! And you can find the solution workbook below. Have a good day!

             

             

            Please dont forget to mark correct and/or helpful!

             

            Best Regards,

            Yasin.

            1 of 2 people found this helpful
            • 3. Re: Dynamic Formatting
              gareth.brunsden.0

              Hi, I need it all performed in one calculated field not multiple different fields with the usual formatting applied to each. Any idea how to do this?

              • 4. Re: Dynamic Formatting
                gareth.brunsden.0

                Hi, Yes, I had tried converting to STR but I get an error (see attached).

                I need it all performed in one calculated field not multiple different fields with the usual formatting applied to each.

                • 5. Re: Dynamic Formatting
                  Abhilash Sharma

                  Hi Gareth,

                   

                  As I had replied earlier, the use of STR function will help you achieve the required result. Please refer below screenshot:

                   

                  All you got to do additionally is just format the field using LEFT() function.

                   

                  Please mark correct and/or helpful.

                  -Abhilash

                  1 of 1 people found this helpful
                  • 6. Re: Dynamic Formatting
                    YASIN CAN PARLAK

                     

                    Check this one! You will create K,B,M string field but find the answer with one IF computation.

                     

                    I named it concat, and try to follow instructions as you said.

                    1 of 1 people found this helpful
                    • 7. Re: Dynamic Formatting
                      YASIN CAN PARLAK

                      You can delete [K value],[M value] and [B value] and rather just put 'K' , 'M', 'B' at the end of sentences.  To sum, just one calculated field is all you need!

                      1 of 1 people found this helpful
                      • 8. Re: Dynamic Formatting
                        Abhilash Sharma

                        Hi Gareth,

                         

                        I strongly urge you to closely refer the screenshot that I posted earlier.

                         

                        Things incorrect in your calculated field:

                        1. All THEN clauses need to have STR() function. You have not used it in the 1st condition and the ELSE condition

                        2. Also in the 2nd last condition you have not spelled it correctly i.e. SRT instead of STR()

                         

                        Refer to the calculated field definition below and replace Sales by Revenue:

                        IF  SUM([Sales]) < 1000 then STR(SUM([Sales]))

                        ELSEIF SUM([Sales]) >= 1000 and SUM([Sales]) < 1000000 then (STR(SUM([Sales]))) + 'K'

                        ELSEIF SUM([Sales]) >= 1000000 and SUM([Sales]) < 1000000000 then (STR(SUM([Sales]))) + 'M'

                        ELSEIF SUM([Sales]) > 1000000000 then (STR(SUM([Sales]))) + 'B'

                        else STR(SUM([Sales]))

                        end

                         

                        -Abhilash

                        2 of 2 people found this helpful
                        • 9. Re: Dynamic Formatting
                          Tim Dines

                          That will work if you convert the first line [REVENUE] to a string and the one in the else line to a string as well.

                          1 of 1 people found this helpful