Updated with Packaged workbook for your ready reference and help (u may see various cal. but none are close enough, however, I am sure, little more modifications to these cal., may be good enough for a decent solution. The denormalized sheet may not work - as I was looking for solution in denormalized tables also, i just included this sheet) - Thanks
Thanks Matt, I tried before posting here and couldn't do much as I was working on an extract!
However, I will take this opportunity to narrate this workbook clarity:
1st sheet - VARIANCE FLAG COLORS
Focus_product_attribute_description (from table: EMDM_Focus_Product_Attributes)
Focus_product_attribute_details (from table: EMDM_Focus_Product_Attributes_details)
COT_Description (from table: EMDM_YTD_Results_Dsb)
Values on the Dashboard:
Results_Fact (from table: EMDM_YTD_Results_Dsb)
Hidden - Var_Flag (from table: EMDM_Focus_Product_Attributes_details)
(Var_Flag column has values 1, 2 and 4 - Each record will be flagged in database based on (1) >0% Var. 2) -5% to 0% Var. 3) < -5% Var.) with one of this values i.e., either 1 or 2 or 4)
VARIANCE FLAG CAL. (purpose is to bring-in Var_Flag column with a color code i.e., Red, Green and Yellow which needs to be displayed at a row level on the dashboard)
2nd sheet - Variance Normalized
In this sheet Var_Flag column is not available so not hidden. The purpose is to eliminate the calculations in database based on 1) >0% Var. 2) -5% to 0% Var. 3) < -5% Var. and flag them for reporting purposes.
I want to determine based on the column 'Growth %' which type of category each record belongs to:
1) >0% Var. 2) -5% to 0% Var. 3) < -5% Var.
3rd sheet - Variance in Denormalized
If we cannot come up with normalized database from 1st sheet kind, I want to see if the same can be achieved from a Denormalized table wherein all the values will be in one Table and no need of any joins so that the columns are available right in the measures unlike measures columns like 1st sheet, and we can be able to determine the 'growth %' which takes Previous Year and Current Year sales difference in % and categorize them into 1) >0% Var. 2) -5% to 0% Var. 3) < -5% Var. with color coding Red, Green and Yellow respectively.
I hope you will appreciate the above and bring some thoughts. You may see some of my calculations which are not working right for an idea. Thanks and appreciate your quick inputs.
Is there any possibility to show a variance column on a dashboard in different colors which should go with the specified range of change if variance is about x % of change then RED, if it is y% of change then BLUE, etc.?
I am trying to figure out the ways and means to achieve a variance between two columns that derive from a fact table.
- Region table with Region names
- Attributes table with sales for this year and previous along with Variance columns (pre-calculated)
Now, I am trying to see if I can color code the variance based on conditions like >0%, -5% to 0% and <-5%
I would be easy for me had I cal. the sales columns (previous and current) and derive the % of change, but this is coming from separate tables and need to achieve a color for specific condition as detailed above.
GV Sireesha's Region (comes from Region Table)
Current Previous Variance Variance % (all these columns comes from Attributes table)
786 1436 -650 -45% (all these columns comes from Attribute Fact table)
Thank and regards,