How to display two total numbers, such as spend and savings, on a bar chart.

RavenUSMC
RavenUSMC
in Card Building

I have a grouped bar chart that is essentially showing spend and savings broken out by categories such as hotel, food and beverage etc. The summary number displays the total spend. However, my issue is that I need to display the total savings as well. Does anyone know how to do this? It can be in the summary number or the legend area. Thank you for any help. 

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷
    Answer ✓

    You can display multiple summary numbers by using the concat function along with an aggregate function. It would look something like this:

    CONCAT('$',ROUND(SUM(`YourColumn`),0), ' Total for this column | $',ROUND(SUM(`YourOtherColumn`),0),' Total for the other column')

    The downside is that it won't automatically format your numbers iwth commas, etc. I can post another example of how to account for that if you need to see that.

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • RavenUSMC
    RavenUSMC

    Yes! That seems to work. How would you corrrectly format your numbers so that it has commas? Thank you for the answer!

  • RavenUSMC
    RavenUSMC

    Yes, please post the example of how to show commas. This was really helpful and worked! 

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    Here is how you can format numbers into currency values and using the B, M and K abbreviations for billions, millions and thousands since showing the full numbers would make the summary number extremely long.

    In this example, the resulting summary number would look like this:

    $10.74M Total Written Premium | $19.47M Total NLC-MIC Incurred

    Here is how you would write your beast mode:

     

    CONCAT('$'
    ,CASE
    WHEN INSTR(SUM(`Written Premium`),'.') = 13 THEN /*xxx billion*/
    CONCAT(LEFT(SUM(`Written Premium`),3),'.',SUBSTRING(SUM(`Written Premium`),4,2),'B')
    WHEN INSTR(SUM(`Written Premium`),'.') = 12 THEN /*xx billion*/
    CONCAT(LEFT(SUM(`Written Premium`),2),'.',SUBSTRING(SUM(`Written Premium`),3,2),'B')
    WHEN INSTR(SUM(`Written Premium`),'.') = 11 THEN /*x billion*/
    CONCAT(LEFT(SUM(`Written Premium`),1),'.',SUBSTRING(SUM(`Written Premium`),2,2),'B')
    WHEN INSTR(SUM(`Written Premium`),'.') = 10 THEN /*xxx million*/
    CONCAT(LEFT(SUM(`Written Premium`),3),'.',SUBSTRING(SUM(`Written Premium`),4,2),'M')
    WHEN INSTR(SUM(`Written Premium`),'.') = 9 THEN /*xx million*/
    CONCAT(LEFT(SUM(`Written Premium`),2),'.',SUBSTRING(SUM(`Written Premium`),3,2),'M')
    WHEN INSTR(SUM(`Written Premium`),'.') = 8 THEN /*x million*/
    CONCAT(LEFT(SUM(`Written Premium`),1),'.',SUBSTRING(SUM(`Written Premium`),2,2),'M')
    WHEN INSTR(SUM(`Written Premium`),'.') = 7 THEN /*xxx thousand*/
    CONCAT(LEFT(SUM(`Written Premium`),3),'.',SUBSTRING(SUM(`Written Premium`),4,2),'K')
    WHEN INSTR(SUM(`Written Premium`),'.') = 6 THEN /*xx thousand*/
    CONCAT(LEFT(SUM(`Written Premium`),2),'.',SUBSTRING(SUM(`Written Premium`),3,2),'K')
    WHEN INSTR(SUM(`Written Premium`),'.') = 5 THEN /*x thousand*/
    CONCAT(LEFT(SUM(`Written Premium`),1),'.',SUBSTRING(SUM(`Written Premium`),2,2),'K')
    END
    ,' Total Written Premium | $'

    ,CASE
    WHEN INSTR(SUM(`TotalIncurredForYear`),'.') = 13 THEN /*xxx billion*/
    CONCAT(LEFT(SUM(`TotalIncurredForYear`),3),'.',SUBSTRING(SUM(`TotalIncurredForYear`),4,2),'B')
    WHEN INSTR(SUM(`TotalIncurredForYear`),'.') = 12 THEN /*xx billion*/
    CONCAT(LEFT(SUM(`TotalIncurredForYear`),2),'.',SUBSTRING(SUM(`TotalIncurredForYear`),3,2),'B')
    WHEN INSTR(SUM(`TotalIncurredForYear`),'.') = 11 THEN /*x billion*/
    CONCAT(LEFT(SUM(`TotalIncurredForYear`),1),'.',SUBSTRING(SUM(`TotalIncurredForYear`),2,2),'B')
    WHEN INSTR(SUM(`TotalIncurredForYear`),'.') = 10 THEN /*xxx million*/
    CONCAT(LEFT(SUM(`TotalIncurredForYear`),3),'.',SUBSTRING(SUM(`TotalIncurredForYear`),4,2),'M')
    WHEN INSTR(SUM(`TotalIncurredForYear`),'.') = 9 THEN /*xx million*/
    CONCAT(LEFT(SUM(`TotalIncurredForYear`),2),'.',SUBSTRING(SUM(`TotalIncurredForYear`),3,2),'M')
    WHEN INSTR(SUM(`TotalIncurredForYear`),'.') = 8 THEN /*x million*/
    CONCAT(LEFT(SUM(`TotalIncurredForYear`),1),'.',SUBSTRING(SUM(`TotalIncurredForYear`),2,2),'M')
    WHEN INSTR(SUM(`TotalIncurredForYear`),'.') = 7 THEN /*xxx thousand*/
    CONCAT(LEFT(SUM(`TotalIncurredForYear`),3),'.',SUBSTRING(SUM(`TotalIncurredForYear`),4,2),'K')
    WHEN INSTR(SUM(`TotalIncurredForYear`),'.') = 6 THEN /*xx thousand*/
    CONCAT(LEFT(SUM(`TotalIncurredForYear`),2),'.',SUBSTRING(SUM(`TotalIncurredForYear`),3,2),'K')
    WHEN INSTR(SUM(`TotalIncurredForYear`),'.') = 5 THEN /*x thousand*/
    CONCAT(LEFT(SUM(`TotalIncurredForYear`),1),'.',SUBSTRING(SUM(`TotalIncurredForYear`),2,2),'K')
    END

    ,' Total NLC-MIC Incurred')

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • RavenUSMC
    RavenUSMC

    Great! Thank you so much!

  • ST_-Superman-_
    ST_-Superman-_ 🟤

    you could also use gauge cards and the story dashboard to provide multiple summaries...1.png

     

    2.png

     


    ______________________________________________________________________________________________
    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
    ______________________________________________________________________________________________
This discussion has been closed.

Categories