Using COUNTPARTITION and SUMPARTITION for Multi-Scenario Analysis in Bold BI
This article explains how to use window expressions (COUNTPARTITION and SUMPARTITION) in Bold BI to solve common analytical requirements such as:
- Detecting unique vs shared relationships
- Validating business rules within grouped data
- Performing row-level analysis without aggregation
Key Concepts
Partitioning
- Partitions group rows logically without collapsing them:
PartitionBy := [Location]
- This enables calculations across related records while preserving row-level visibility.
Function Overview
| Function | Purpose | Use Case |
|---|---|---|
| COUNTPARTITION | Counts records within a partition | Uniqueness detection |
| SUMPARTITION | Aggregates numeric values within a partition | Rule validation, accumulation |
Note:
These expressions use Bold BI’s calculated field syntax, which is different from standard SQL.
Scenarios and Use Cases
Scenario 1: Identify Sole Source (Basic)
- Goal: Identify whether each Location is associated with only one Revenue Stream (Sole Source).
IF(
COUNTPARTITION([RevenueStream], PartitionBy := [Location]) = 1,
'Yes',
'No'
)
Scenario 2: Identify Shared Locations
Goal: Identify whether a Location is shared across multiple Revenue Streams.
IF(
COUNTPARTITION(
[RevenueStream],
PartitionBy := [Location]
) > 1,
'Shared',
'Exclusive'
)
This scenario demonstrates how COUNTPARTITION can be combined with SUMPARTITION to evaluate conditions across grouped records while still returning row-level results.
Scenario 3: Count Primary Records per Location
- Goal: Use SUMPARTITION to count how many primary records exist within each Location.
SUMPARTITION(
[IsPrimary],
PartitionBy := [Location]
)
This scenario extends Scenario 2 by returning the total count instead of a boolean result.
Scenario 4: Validate Primary Ownership (Using SUMPARTITION)
- Goal: Validate that each Location has exactly one record marked as primary.
This scenario assumes an IsPrimary numeric field exists where 1 represents the primary record and 0 represents a non-primary record. demonstrates how SUMPARTITION can directly validate business rules using numeric flags, without requiring nested logic.
IF(
SUMPARTITION([IsPrimary], PartitionBy := [Location]) = 1,
'Valid',
'Invalid'
)
Scenario 5: Combined Business Rule
- Goal: Identify Locations that are both uniquely associated with a Revenue Stream and have a valid primary ownership record.
IF(
COUNTPARTITION([RevenueStream], PartitionBy := [Location]) = 1
AND
SUMPARTITION([IsPrimary], PartitionBy := [Location]) = 1,
'Valid Sole Source',
'Not Valid'
)
Implementation Steps
Step 1: Create or Edit a Dashboard
- Click on the edit icon to open the Dashboard in design mode
Step 2: Bind Data
- Add a Grid Widget
- Click on the settings icon and navigate to the assign data panel.
- Add required fields: Revenue Stream, Location (Optional: metrics like Revenue, IsPrimary)
Step 3: Add Calculated Fields
- Navigate to Expressions/Calculated Fields
- Click Add New
- Paste expressions from the scenarios
Step 4: Validate Output
- Add each calculated field to the Grid widget as required and validate the output step-by-step.
- Preview the Grid to verify output.
It is recommended to verify each scenario individually before combining logic to ensure accuracy.
Notes & Best Practices
-
Use COUNTPARTITION when:
- Checking uniqueness or duplicates
-
Use SUMPARTITION when:
- Validating aggregated flags
- Counting derived conditions
-
Always:
- Partition by the correct business dimension
- Validate logic using a debug column (count/sum)
-
Build logic incrementally:
- Count → Validate
- Add condition
- Aggregate further if required
Limitations
- Duplicate rows from joins:
- Can inflate partition counts
- Leads to incorrect results
- No row consolidation in table/grid
- Rows with different values remain separate
- Cannot merge results into a single row dynamically
- Partition results are row-level
- No automatic grouping like SQL GROUP BY
Conclusion
COUNTPARTITION is best for detecting uniqueness while SUMPARTITION is best for evaluating aggregated conditions. Combining both enables advanced business logic directly in dashboards.
When used with clean data and correct partitioning, these functions provide a powerful way to build dynamic, row-level analytics without data aggregation.
Related Articles
