Forum Discussion

eliasayyy's avatar
eliasayyy
Memorable Member
3 years ago
Solved

Dynamic Format Data Labels in charts

hello everyone , 
i have a facebook ads by campaign table

Campaign            Impressions
Camp A1500000
Camp B100500
Camp C1000
Camp D10000


i want to make a  column chart that will show me the campaign in the x-axis and measure of Total impressions
Total impressions = sum(table[impressions])

when i turn on data labels , i want to show a dynamic data label

so if number is in millions it will return 1.00M if the numbe rin thousands then it will return 1.00k

so new format should be i will show in table for better context

Campaign          Impressions
Camp A1.5M
Camp B100.5k
Camp C1.00K
Camp D10.00K




  • amitchandak's avatar
    amitchandak
    3 years ago

    eliasayyy , Try like

     

    SWITCH ( TRUE() ,
    [Impressions]>= 1000000000 , FORMAT ([Impressions]/1000000000, decimal) & "bn" ,
    [Impressions]>= 1000000, FORMAT ([Impressions]/1000000, decimal) & "M" ,

    [Impressions]>= 1000, FORMAT ([Impressions]/1000, decimal) & "K",
    FORMAT ([Impressions] , decimal)
    )

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    eliasayyy , Try like

     

    SWITCH ( TRUE() ,
    [Impressions]>= 1000000000 , FORMAT ([Impressions]/1000000000, decimal) & "bn" ,
    [Impressions]>= 1000000, FORMAT ([Impressions]/1000000, decimal) & "M" ,

    [Impressions]>= 1000, FORMAT ([Impressions]/1000, decimal) & "K",
    FORMAT ([Impressions] , decimal)
    )

     

    • ewilliams0405's avatar
      ewilliams0405
      Frequent Visitor
      3 years ago

      Actually with the dynamic formating you don't need to use division.

       

      SWITCH(TRUE(),
      ABS([val]) >= 1000000, "\$#,0,,.0M;(\$#,0,,.0M)"
      ABS([val]) > 100000,"$#,0,.0k;($#,0,.0k)"
      ,"#,0;(#,0)")
       
      I have noticed with trying to get K to work without a decimal using, 
      "\$#,0,.k;(\$#,0,.k)" the values that appear in the data label is not correct but the value shown in tables and in the tool tip is corrrect.  I have no Idea why but if someone knows a solution please let me know.  
       
      Jack