Variance % formula not working correctly at Grand Total

Ankur · October 17

What I am trying to do is look at Monthly Headcount Trend (Variance %) by Billable, Non-Billable 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.

Grand Total Variance % Issue.xlsx

Also sharing the workbook for referance. Looking for some help here

ggenovese · October 18

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.

DavidChurchman · October 17

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 country-level variance, and a separate card with the monthly total variance in parallel.

Ankur · October 18

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
1-Jan-24	23	Y	A
1-Jan-24	3	N	A
1-Feb-24	80	Y	B
1-Feb-24	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.

ggenovese · October 18

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.

Variance % formula not working correctly at Grand Total

Best Answer

Answers

Categories