Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
I'm building a custom KPI table visual that includes a sparkline. To render the sparkline, I need the raw (unaggregated) data by date. However, I also need aggregated totals to display in a separate "Totals" column.
Right now, I'm using a categorical dataViewMapping grouped by date, which gives me each individual value. But I also need the total (e.g., sum or a custom DAX measure) across all dates.
I tried something like this in capabilities.json:
Solved! Go to Solution.
This work around worked for me. This approach makes the aggregatedTotals measure repeat the same total for each date row — so you can show both the sparkline and the overall KPI side-by-side.
1. Create a DAX measure for the aggregate over a fixed date range (I used variables instead of hardcoded dates):
TotalBetweenDates =
CALCULATE(
[Total],
FILTER(
ALL('Date'),
'Date'[Date] >= DATE(2025,1,1) && 'Date'[Date] <= DATE(2025,3,31)
)
)
2. Add the new data role in capabilities.json:
{
"displayName": "Aggregated Totals",
"name": "aggregatedTotals",
"kind": "Measure"
}
3. Include the aggregate measure in your dataViewMappings:
"dataViewMappings": [
{
"categorical": {
"categories": { "for": { "in": "metric" } },
"values": {
"group": {
"by": "date",
"select": [
{ "for": { "in": "total" } },
{ "for": { "in": "target" } },
{ "for": { "in": "kpi" } },
{ "for": { "in": "aggregatedTotals" } }
]
}
}
}
}
]
This work around worked for me. This approach makes the aggregatedTotals measure repeat the same total for each date row — so you can show both the sparkline and the overall KPI side-by-side.
1. Create a DAX measure for the aggregate over a fixed date range (I used variables instead of hardcoded dates):
TotalBetweenDates =
CALCULATE(
[Total],
FILTER(
ALL('Date'),
'Date'[Date] >= DATE(2025,1,1) && 'Date'[Date] <= DATE(2025,3,31)
)
)
2. Add the new data role in capabilities.json:
{
"displayName": "Aggregated Totals",
"name": "aggregatedTotals",
"kind": "Measure"
}
3. Include the aggregate measure in your dataViewMappings:
"dataViewMappings": [
{
"categorical": {
"categories": { "for": { "in": "metric" } },
"values": {
"group": {
"by": "date",
"select": [
{ "for": { "in": "total" } },
{ "for": { "in": "target" } },
{ "for": { "in": "kpi" } },
{ "for": { "in": "aggregatedTotals" } }
]
}
}
}
}
]
Hi @cchilton,
Thank you for reaching out to the Microsoft Fabric Forum Community.
I understand that you're building a custom KPI table visual and facing a challenge in rendering both raw (unaggregated) data for sparklines and aggregated totals in a separate column. The issue arises because Power BI only activates one data View at a time, making it tricky to access both the raw data by date (for sparklines) and aggregated totals.
To get the aggregated totals create a DAX measure like this:
TotalSum = CALCULATE(SUM('YourTable'[Value]), ALL('YourTable'[Date]))
This measure calculates the sum of the values across all dates, regardless of the date context, which is important for displaying the total in the Totals column.
The capabilities.json file, ensure that you include both the raw data for sparklines (grouped by date) and the aggregated total (as a separate measure). Here’s how you can structure the dataViewMappings:
{
"dataViewMappings": [
{
"categorical": {
"categories": {
"for": { "in": "metric" }
},
"values": {
"group": {
"by": "date",
"select": [
{ "for": { "in": "total" } }, // Raw data for sparklines
{ "for": { "in": "target" } },
{ "for": { "in": "kpi" } }
]
},
"select": [
{ "for": { "in": "totalSum" } } // Aggregated total (e.g., TotalSum measure)
]
}
}
}
],
"dataRoles": [
{ "name": "metric", "kind": "Grouping", "displayName": "Metric" },
{ "name": "date", "kind": "Grouping", "displayName": "Date" },
{ "name": "total", "kind": "Measure", "displayName": "Total" },
{ "name": "target", "kind": "Measure", "displayName": "Target" },
{ "name": "kpi", "kind": "Measure", "displayName": "KPI" },
{ "name": "totalSum", "kind": "Measure", "displayName": "Total Sum" }
]
}
In your visual's TypeScript/JavaScript code, you can access both the raw data for sparklines and the aggregated total.
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
I already tried to have 2 select statements like you've shown above. I get the below errors:
error should have required property '.for' .dataViewMappings[0].categorical.values
error should have required property '.bind' .dataViewMappings[0].categorical.values
error should match exactly one schema in oneOf .dataViewMappings[0].categorical.values
error Invalid capabilities
Do you have any advice?
User | Count |
---|---|
14 | |
4 | |
2 | |
2 | |
1 |
User | Count |
---|---|
3 | |
3 | |
3 | |
2 | |
2 |