Variance % formula not working correctly at Grand Total

Ankur
Ankur Member
in Beast Mode

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.

image.png

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

Tagged:

Answers

  • DavidChurchman
    DavidChurchman Coach

    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.

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • Ankur
    Ankur Member

    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
    ggenovese Contributor

    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.

    image.png

Categories