Universal Composite KPI : Weighted & Normalized Scoring Using Visual Calculations Only
Universal Composite KPI : Weighted & Normalized Scoring Using Visual Calculations Only This pattern creates a base solution for Composite KPI Score by normalizing multiple KPIs to a 0–1 scale and applying user-defined weights via What-If parameters(numeric field parameters). The result is a single, comparable performance indicator that works across different KPI types and units, fully powered by Visual Calculations. Concept Overview Because KPIs have different scales (money, percentages, counts), each KPI is first normalized, then multiplied by a weight. The final composite score is a weighted sum of all normalized KPIs. Normalization Formula 1. Min–Max Normalization (Higher Is Better) 0 to 1, where 0 is the minimum and 1 is the maximum: Use this when higher values represent better performance (e.g., Sales, Orders, Margin, CSAT). 2. Min–Max Normalization (Lower Is Better) Some KPIs—such as Total Cost, Defects, Error Rate, or Response Time—improve when the value is lower. In these cases, the formula must be inverted so that the lowest value receives the highest normalized score: This keeps everything in the same 0–1 range, while correctly rewarding lower values. 3. Z-Score Normalization (Standardization) Alternatively, you can normalize using Z-scores, which measure how far a value is from the mean: Z-scores: Are centered around 0 Can be negative or greater than 1 Are useful when you care about distance from average, not just min/max boundaries For KPIs where lower is better, you simply invert the sign: example code is provided for Z normalization in the file but not used in the composition Composite Score Formula Visual Calculation Functions Used FIRST() ORDERBY() DIVIDE() Basic arithmetic operations (multiplication, addition) Pros Fair comparison across KPIs with different units User-controlled weight influence Fully dynamic within the visual 100% implemented using Visual Calculations Cons You must repeat the normalization pattern for each KPI you want to include. Each added KPI requires creating a new normalization VC (unless automated in a future pattern).Unlimited Visuals in one chart
Here’s the short “recipe” you can reuse to get unlimited visuals inside one chart. Define patterns, not visuals: -Create a small Selection table with one row per pattern: Selection ID and Selection Name (e.g. “Sales Drivers (2 bars + 2 lines)”, “Profit Engine”, “Pattern 27”, etc.). -Use this table as a slicer. Fix your KPI set -Decide a finite set of base KPIs (Sales, Cost, Profit, Units, Products, etc.). -Create normal measures for each KPI. -Create “router” Visual Calculations -For each KPI and for each role (Bar or Line), create one Visual Calculation that simply says: IF( SelectedID is in the list of patterns where this KPI should appear in this role, then show the KPI, otherwise BLANK ). Example: VC Sales (Bar) = IF( SelectedID IN {1,4,6}, [Total Sales], BLANK() ) -Wire the visual once -Put all Bar VCs in the Column Y-axis. -Put all Line VCs in the Line Y-axis. -You never touch the axes again. To add a new visual pattern later Add one row to the Selection table. Update the IN {…} lists in the affected Visual Calculations. Result One combo chart that can morph into any number of visual layouts (6, 20, 60, 100+) just by changing the selection.Table (matrix) with TopN and others
This one is a bit tricky but solves a common wish of showing af table with the Top number of x plus others. There are several tricks involved in this one - first we use a matrix and add the numeric fieldparameter value to the rows and then the attribute we want to group the items by - hide the +/- icon and then we use different visual calculations to show the attributes value if is matches the rank number of the product. Visual calculation functions used: EXPAND RANK ISINSCOPE RUNNINGSUM A large dataset will affect the performance and you might want to consider alternatives Enjoy visual calcs ErikMatrix box plot
In this item I am using a different visual calculations to at the end construct a text string based on unichar's so it looks like a box plot. In this way we can simulate a vertical box plot in a table where the sorting features of a table could be relevant. This might also inspire you to use a unichar text string to illustrate other elements - for instance a gantt chart Enjoy visual calc's ErikBoxplot
This one uses visual calculations to create a box plot within a line and stacked column chart. The box plot grouping is determined by a field parameter so it is very dynamic. The main trick is to add the value to plot to the x-axis fields under the grouping and then the visual will contain all the necessary data to calculate the values for the box plot values. If you dataset is large there might be a performance issue. Have fun with the vc's ErikSwitch between line and column chart
This one uses two visual calculations to plot the fact either as the column value or as the line value to enable a switch between a column or a line chart. Its controlled by a numeric field parameter - with the values 0 and 1 - and then the slicer uses a custom number format to show the values as "Line" or "Chart". The value of the field parameter is added to the tooltip section of the chart and can then be uses in the VC's to return the value or blank depending on they choose line or chart. Have VC fun ErikHighlight min and max
This one uses the RANK function to find the highest value and lowest value and then calculates a color code if the rank = 1 and when its equal to the number of rows in the visuals data. An alternative to use the RANK function is to use the MINX and MAXX to find out whether the the Sales Value is equal to those values. Remember to set the data format to text for the color calculations Have VC fun ErikTopN and Others plus dynamic title
This report shows you how you can use visual calculations together with field paramters to highlight x no of items in your bar chart. It will also use visual calculations to generate a dynamic text that can be used as a subtitle in the visual to add context of what is highlighted and the share of totals. Uses: RANK EXPAND @COLLAPSEALL not working for smaller numbers in a Visual Calculation
I've been trying to write a visual calculation formula, but I've noticed that for smaller numbers it doesn't work as expected. I've been able to pin the problem to COLLAPSEALL behavior. It basically stops working for smaller numbers in my table. It works well for many rows, but when it reaches values smaller than 0.01% of the column total, the Total column shows the row value, instead of the expected Total value (collapsed column total). The test formula is as simple as this: Total = COLLAPSEALL([Value],ROWS) The screenshot shows a data sample, where it breaks (rows sorted by value, descending).
