Difference of % of column total

Chayan
Chayan Member
in Cards, Dashboards, Stories

I have two column where I use % of column total to calculate the Share of Markets now I wish to get the difference of the percentages for example 31.91% - 31.10% will give me 0.81% difference. I just cant simply do that, any Idea how to implement this?

image.png

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Ah that makes sense now.

    You'll likely need to use a different beast mode to calculate the percent of total using something called a window function. A window function will return an aggregation across your entire dataset or subsection of data.

    Current Year

    SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE) THEN `Value` END) / SUM(SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE) THEN `Value` END)) OVER ()

    Last Year

    SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE)-1 THEN `Value` END) / SUM(SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE)-1 THEN `Value` END)) OVER ()

    Difference:

    (SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE) THEN `Value` END) / SUM(SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE) THEN `Value` END)) OVER ())
-
(SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE)-1 THEN `Value` END) / SUM(SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE)-1 THEN `Value` END)) OVER ())

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • ColemenWilson
    ColemenWilson Coach
    edited November 2023

    You can create a beastmode (calculated field) like this:

    SUM(YTD_22) - SUM(YTD_23)

    Then drag the beastmode field into your table and it will show the difference between the two columns in your screenshot.

    If I solved your problem, please select "yes" above

  • GrantSmith
    GrantSmith Coach

    The concept is the same if you're using beast modes to calculate the YTD_22 and YTD_23 values, just create a new beast mode by subtracting your 22 beast mode from 23 beast mode as @ColemenWilson mentioned.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Chayan
    Chayan Member

    here's the thing see the YTD_22 column is '% of column total' see below

    image.png

    Now I wish to see the difference just by SUM(YTD_22) - SUM(YTD_23) is not working because I'm using % of column total to get a share of market. In this case, this formula doesn't work, any other way around?

  • Chayan
    Chayan Member

    ColemenWilson, @GrantSmith. Any idea how to resolve this? any help will be highly appreciated! thanks in advance.

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Ah that makes sense now.

    You'll likely need to use a different beast mode to calculate the percent of total using something called a window function. A window function will return an aggregation across your entire dataset or subsection of data.

    Current Year

    SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE) THEN `Value` END) / SUM(SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE) THEN `Value` END)) OVER ()

    Last Year

    SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE)-1 THEN `Value` END) / SUM(SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE)-1 THEN `Value` END)) OVER ()

    Difference:

    (SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE) THEN `Value` END) / SUM(SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE) THEN `Value` END)) OVER ())
-
(SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE)-1 THEN `Value` END) / SUM(SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE)-1 THEN `Value` END)) OVER ())

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories