Variance % formula not working correctly at Grand Total
What I am trying to do is look at Monthly Headcount Trend (Variance %) by Billable, NonBillable and Total. My approach to identify change % is : (Current Month  Previous Month) / Previous Month. Since, it will be showing multiple months data in a tabular form, so I used the following formula for Variance %
(SUM( CASE WHEN
Billable Flag
='Y' THEN IFNULL(
Headcount
,0) END )
LAG( SUM( CASE WHEN
Billable Flag
='Y' THEN IFNULL(
Headcount
,0) ELSE 0 END )) over( Partition by
Cost Center Family
ORDER BY MONTH(
Date
))
) * 1.
/
LAG( SUM( CASE WHEN
Billable Flag
='Y' THEN IFNULL(
Headcount
,0) ELSE 0 END )) over( Partition by
Cost Center Family
ORDER BY MONTH(
Date
))
This formula works fine at row level but at Grand Total level the Variance % shows incorrect number which is not even close to the actual output. But when I check the formula to see Variance Number by removing the Denominator, the output is correct. Which means it is only creating issue when it is getting divided. But at row level (which is Cost Center Family wise), the Variance % is showing correct output.
In the above image, I have mentioned 2 Grand Total. The Expected is what should ideally come but I am getting the output as What is Showing.
I am using Pivot Table if that helps.
Also sharing the workbook for referance. Looking for some help here
Best Answer

This is interesting, I did a test using the Domo Dimensions Calendar dataset using this variance beast mode:
(
 current value
count(`d`)

 previous value
LAG( count(`d`)) over( Partition by `dayName` ORDER BY MONTH(`dt`))
)
/
 previous value
LAG( count(`d`)) over( Partition by `dayName` ORDER BY MONTH(`dt`))And I am seeing similar results as yours, the numerator and denominator appear to be working exactly as expected even in the Total row, but the variance while correct for each row is giving the wrong total. In my case the total appears to be the SUM of the Numerator (35% vs the expected 3%), not sure why that is but I'm sharing my findings in case someone with more knowledge on the behavior of LAG functions might comment.
0
Answers

Rather than the output, a sample of the data in the format you're using would be more helpful. Explaining the column names used in your BeastMode would also help.
I'm not sure what 'Cost Center Family' is, but I'm assuming that partitioning over it is what is causing the issue in the total row. In my experience, Pivot Tables can't correctly aggregate anything but the most basic functions in the total rows. Another approach would be to have the Pivot Table with the countrylevel variance, and a separate card with the monthly total variance in parallel.
Please 💡/💖/👍/😊 this post if you read it and found it helpful.
Please accept the answer if it solved your problem.
0 
Hi @DavidChurchman,
Thanks for your response.
Few clarification 
By mistake I showed the output table as Country. Please consider it to be Cost Center Family. Now Cost Center Family is a Superset of Cost Centers, or you can say it is a group of cost centers. For example, we can have HR cost centers by Location & sub practices but all together those are HR cost centers. So that's what Cost Center Family is all about.
Sharing a sample data of my actual dataset it not possible as it contains too many columns but let me share the columns which I am using for this view.
Date
Headcount
Billable Flag
Cost Center Family
1Jan24
23
Y
A
1Jan24
3
N
A
1Feb24
80
Y
B
1Feb24
7
N
B
The alternate approach that you have suggested is not possible as Leadership wants to view the data as the output sample I shared earlier.
0 
This is interesting, I did a test using the Domo Dimensions Calendar dataset using this variance beast mode:
(
 current value
count(`d`)

 previous value
LAG( count(`d`)) over( Partition by `dayName` ORDER BY MONTH(`dt`))
)
/
 previous value
LAG( count(`d`)) over( Partition by `dayName` ORDER BY MONTH(`dt`))And I am seeing similar results as yours, the numerator and denominator appear to be working exactly as expected even in the Total row, but the variance while correct for each row is giving the wrong total. In my case the total appears to be the SUM of the Numerator (35% vs the expected 3%), not sure why that is but I'm sharing my findings in case someone with more knowledge on the behavior of LAG functions might comment.
0
Categories
 All Categories
 1.8K Product Ideas
 1.8K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 296 Workbench
 6 Cloud Amplifier
 8 Federated
 2.9K Transform
 99 SQL DataFlows
 614 Datasets
 2.2K Magic ETL
 3.8K Visualize
 2.5K Charting
 727 Beast Mode
 53 App Studio
 40 Variables
 677 Automate
 173 Apps
 451 APIs & Domo Developer
 45 Workflows
 8 DomoAI
 34 Predict
 14 Jupyter Workspaces
 20 R & Python Tiles
 394 Distribute
 113 Domo Everywhere
 275 Scheduled Reports
 6 Software Integrations
 121 Manage
 118 Governance & Security
 Domo Community Gallery
 32 Product Releases
 10 Domo University
 5.4K Community Forums
 40 Getting Started
 30 Community Member Introductions
 108 Community Announcements
 4.8K Archive