Difference of % of column total

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?
Best 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!**0
Answers
-
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
0 -
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!**0 -
here's the thing see the YTD_22 column is '% of column total' see below
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?
0 -
ColemenWilson, @GrantSmith. Any idea how to resolve this? any help will be highly appreciated! thanks in advance.
0 -
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!**0
Categories
- All Categories
- Product Ideas
- 2.1K Ideas Exchange
- Connect
- 1.3K Connectors
- 309 Workbench
- 7 Cloud Amplifier
- 10 Federated
- Transform
- 663 Datasets
- 119 SQL DataFlows
- 2.3K Magic ETL
- 823 Beast Mode
- Visualize
- 2.6K Charting
- 86 App Studio
- 46 Variables
- Automate
- 193 Apps
- 483 APIs & Domo Developer
- 87 Workflows
- 23 Code Engine
- AI and Machine Learning
- 23 AI Chat
- 4 AI Projects and Models
- 18 Jupyter Workspaces
- Distribute
- 117 Domo Everywhere
- 283 Scheduled Reports
- 11 Software Integrations
- Manage
- 143 Governance & Security
- 11 Domo Community Gallery
- 49 Product Releases
- 13 Domo University
- Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 116 Community Announcements
- 5K Archive