Variance from total

Is there anyway to calculate what the variance of a row is from the total of the whole column?

Here’s my example:

I want to see what the variance is from the % share columns to the heading, E.g. Line 1 is up by 13.95 but line 2 is down by 8.99

This is in Customer Mode:

Hey JMills, I have been trying to come up with an answer for you but I feel like I am not understanding the question.
Is it possible for you to provide some more information? Even some dummy data showing what you are trying to accomplish?

This currently can’t be done dynamically in Phocas. You would need to get the value and hard code it in the expression e.g. ((B/A)*100)-33.23 which would be incorrect as soon as you made selections.

It would be great if matrix share mode could be setup like you have your custom mode. They’re close but obviously matrix share doesn’t include the value and the total without a matrix. I’ll put forward a product suggestion for that and a “variance to total”.

The screenshot above shows what I’m looking for. The total line shows what the percentage of sales each product category represents. From the total I want to compare each customer’s percentage of sale so I can see how far away they are from the total piece. This will show me customers who aren’t buying the same % as the best customers. In SQL I would nest this total as a left join and then do a variance from that.

Thanks Will. Hardcoding it the only option I could fine too. This then leads to us having to download it as Excel and manipulate it from there. & @jmills

Hang on a mo, I may be barking up the wrong tree, please ignore if I am not understanding your inital requirement. Wouldn’t this get you what you need?
It may be nasty custom code but think its dooable.



@JonKemp good point. Yes it would be possible by dropping into “nasty custom code”. However this functionality is not documented (see Custom Mode) and its use has limited support.

I think the column expression would be something like:

^((sum({b}) over()/sum({a}) over())*100)-(({b}/{a})*100)

@JonKemp I did read your article first actually but it error’s out when I attempt to do what I’m looking for. If I use formula I get: “An error has occurred when querying the data”

I tried my own but still get the same results.

Hi @jmills ,

Difficult to support without being on your system. Have a look at the error logs (if you can) as this should tell you what happened to some degree.

When I was struggling with it…and I did … a lot :wink: .I would suggest that you break the formula down, using Will’s code, do the first part which is:
^Sum({b}) over()

See what you get, if that works then add the next bit and the next until whammo you get the error. I also will point out that the error could be a DIV 0 error, dividing by zero. I often put this in as well to error proof which is :
NULLIF({a}.0) which this does is makes the returning value become null if its zero, this stops div 0’s in thier tracks :slightly_smiling_face:

Here is one I just did for you:

The code was: ^NULLIF(sum({a}) over (),0)
This returns:

FYI I copied and pasted Wills code and it worked for me so, I am guessing its Div 0.
Good Luck!

1 Like

@JonKemp Thank you Sirs! I have this working now thanks to NULLIF’s:

^((sum(NULLIF({b},0)) over()/sum(NULLIF({a},0)) over())*100)-((NULLIF({b},0)/NULLIF({a},0))*100)


Congrats!!! :tada:

Only problem is you are now “unleashed” into the wild world of custom mode and will be looking for places to use this undocumented feature LOL :slight_smile:

1 Like