How can I fix this Beast Mode Window Function to display the proper value in the total row?
I have the following table that is using window functions in beast mode to calculate pipeline coverage.
I have these window functions for calculating the visible visible columns:
Pipeline/AOP
SUM(CASE WHEN LEFT(`Stage`, 1) != '7' THEN `Pipeline` END) / MIN(`AOP`) OVER (PARTITION BY `Selling Lane`)
Late Stage/AOP
SUM(CASE WHEN LEFT(`Stage`, 1) IN ('3','4','5','6') THEN `Pipeline` END) / MIN(`AOP`) OVER (PARTITION BY `Selling Lane`)
Each Selling Lane has a specific AOP and there are multiple records for each selling lane.
The issue is the total row is showing the sum for each of the Selling Lanes' coverages instead of the total aggregation. I'm assuming this is because I have the partition by clause included in there, which is needed to calculate it for each selling lane. Is there a way that I can get the total row to show the aggregate overall instead of the sum for each of the selling lanes?
Best Answers

The total row adds everything after beast modes have been applied. You’ll need to stack your data in an ETL using an append with your data set grouped and summed and call the rows TOTAL. Then you can use the beast mode to calculate your data for each lane and the total
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 
I don't quite understand what you're trying to accomplish, but this is syntactically incorrect (for analyzer) ... it's fine for standard SQL. But this window function MIN(AOP) will not aggregate as expected.
SUM(CASE WHEN LEFT(`Stage`, 1) IN ('3','4','5','6') THEN `Pipeline` END) / MIN(`AOP`) OVER (PARTITION BY `Selling Lane`)
Window functions in Analyzer must always include two aggregations b/c the windowing happens AFTER the initial GROUP BY clause is applied.
In the below example, by taking SUM(AOP) for each row of your displayed axis, and then taking the MIN of each SUM() you're finding the minimum aggregated AOP across all your rows.
If you did it the other way around SUM(MIN(AOP) you'd find the minimum AOP for each group and then sum the MIN(AOP) across all groups.
SUM(CASE WHEN LEFT(`Stage`, 1) IN ('3','4','5','6') THEN `Pipeline` END) / MIN(SUM(`AOP`)) OVER (PARTITION BY `Selling Lane`)
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Answers

@jaeW_at_Onyx Are you able to answer this?
0 
Did you try this?
SUM(CASE WHEN LEFT(`Stage`, 1) != '7' THEN `Pipeline` END) OVER () / MIN(`AOP`) OVER(PARTITION BY `SF Selling Lane`)
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 
Thanks for your response! Unfortunately that seems not to have worked. Doing that breaks the aggregation by selling lane and shows the overall aggregation * number of records in the total row.
0 
The total row adds everything after beast modes have been applied. You’ll need to stack your data in an ETL using an append with your data set grouped and summed and call the rows TOTAL. Then you can use the beast mode to calculate your data for each lane and the total
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 
I don't quite understand what you're trying to accomplish, but this is syntactically incorrect (for analyzer) ... it's fine for standard SQL. But this window function MIN(AOP) will not aggregate as expected.
SUM(CASE WHEN LEFT(`Stage`, 1) IN ('3','4','5','6') THEN `Pipeline` END) / MIN(`AOP`) OVER (PARTITION BY `Selling Lane`)
Window functions in Analyzer must always include two aggregations b/c the windowing happens AFTER the initial GROUP BY clause is applied.
In the below example, by taking SUM(AOP) for each row of your displayed axis, and then taking the MIN of each SUM() you're finding the minimum aggregated AOP across all your rows.
If you did it the other way around SUM(MIN(AOP) you'd find the minimum AOP for each group and then sum the MIN(AOP) across all groups.
SUM(CASE WHEN LEFT(`Stage`, 1) IN ('3','4','5','6') THEN `Pipeline` END) / MIN(SUM(`AOP`)) OVER (PARTITION BY `Selling Lane`)
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Categories
 All Categories
 1.7K Product Ideas
 1.7K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 292 Workbench
 4 Cloud Amplifier
 8 Federated
 2.8K Transform
 95 SQL DataFlows
 600 Datasets
 2.1K Magic ETL
 3.7K Visualize
 2.4K Charting
 685 Beast Mode
 43 App Studio
 38 Variables
 656 Automate
 170 Apps
 439 APIs & Domo Developer
 42 Workflows
 5 DomoAI
 32 Predict
 12 Jupyter Workspaces
 20 R & Python Tiles
 384 Distribute
 110 Domo Everywhere
 268 Scheduled Reports
 6 Software Integrations
 111 Manage
 108 Governance & Security
 8 Domo University
 25 Product Releases
 Community Forums
 39 Getting Started
 29 Community Member Introductions
 98 Community Announcements
 Domo Community Gallery
 4.8K Archive