Hi folks,
When analyzing our financials, we look for unusual items by comparing individual line items to Operating Income (vertical analysis) and comparing the current month’s activity to an average of the last 12 months (horizontal analysis).
I’ve added a slider to allow folks to filter based on a percentage difference chosen by the user. The default percentage is 25% and users can go from 0 to 100 by fives.
The 25% was an arbitrary choice. What I would like is to refine that percentage on a business unit by business unit basis (for instance, a 10% variance is not material for the larger business units, but a 3% variance would cause concern for our smaller ones).
I’m trying to figure out the math for that calculation but am drawing a blank.
My Vertical % is, for instance, Salaries over Operating Income.
My Horizontal % is Current Month Salaries over the preceding 12 months average of Salaries.
The more I think about it, the more I think the BUDGETED Operating Margin % (Operating Income divided by Operating Revenue) needs to be a part of the calculation.
Vertical %, Horizontal %, Budgeted Operating Margin % all used to define a starting point for my accountants to start their inquiries.
I’m wondering if it’s less a function of a slicer and more of a job for the measure.
If the line item exceeds the Vertical/Horizonta/Bud Oper Marg calculation, then return a value, otherwise return a blank and then configure the visual to NOT display rows that don’t have a value?
I’ve decided that comparing the actual vertical item to the budget operating revenue is the way to go in addition to the historical 12 month average comparison.
Now, I’m trying to figure out how to score those two numbers on a scale of 1 to 100.
Hi @kaDargo - I am a bit confused on what you are trying to achieve. In previous post you mentioned
“If the line item exceeds the Vertical/Horizonta/Bud Oper Marg calculation, then return a value, otherwise return a blank and then configure the visual to NOT display rows that don’t have a value?”
In the latest post, you are talking about scale 1 to 100. If you are looking to use a Slicer with value 1-100 to check variance, then make use of DAX function SelectedValue to get the Slicer value and then use in the measure to perform comparison b/w your required measures and if the value is greater than Slicer variance, then display it else keep as Blank().
The last couple of posts were basically me thinking out loud.
Let me try to clean it up a little. I’m still looking for the math which allows me to compare the Vertical/Horizontal variances. I’m thinking along the lines of a Z Score. I would use that to set the default value for displaying items and then build a slicer to allow the end-user to adjust that as necessary. And, yes, I agree, SELECTEDVALUE would be used to leverage the slicer value.
Does that make sense or did I muddy the waters further?
While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!
.