Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Power Bi Json Theme - Pivot Table Row Subtotal Formatting not working

Hi, I am creating a json Theme file for my company and I am stuck on the matrix table. It just doesnt take the inputs for row subtotals.

Here's the code:

 

 

{
    "name": "Test",
    "pivotTable": {
        "*": {
            "stylePreset": [
                {
                    "name": "Default"
                }
            ],
            "grid": [
                {
                    "gridVertical": false,
                    "gridHorizontal": false,
                    "rowPadding": 4,
                    "outlineWeight": 2,
                    "textSize": 10
                }
            ],
            "columnHeaders": [
                {
                    "fontColor": {
                        "solid": {
                            "color": "#ffffff"
                        }
                    },
                    "backColor": {
                        "solid": {
                            "color": "#142640"
                        }
                    },
                    "outline": "None",
                    "outlineColor": {
                        "solid": {
                          "color": "#0e1622"
                        }
                    },
                    "outlineWeight": 1,
                    "autoSizeColumnWidth": true,
                    "fontFamily": "Calibri",
                    "fontSize": 10,
                    "bold": true,
                    "alignment": "Left",
                    "titleAlignment": "Left",
                    "wordWrap": true
                }
            ],
            "rowHeaders": [
                {
                    "fontColor": {
                        "solid": {
                            "color": "#0e1622"
                        }
                    },
                    "outline": "RightOnly",
                    "outlineColor": {
                        "solid": {
                          "color": "#0e1622"
                        }
                    },
                    "outlineWeight": 1,
                    "stepped": true,
                    "steppedLayoutIndentation": 10,
                    "wordWrap": true,
                    "fontFamily": "Calibri",
                    "fontSize": 10,
                    "alignment": "Left",
                    "showExpandCollapseButtons": true
                }
            ],
            "values": [
                {
                    "fontColorPrimary": {
                        "solid": {
                            "color": "#0e1622"
                        }
                    },
                    "backColorPrimary": {
                        "solid": {
                            "color": "#ffffff"
                        }
                    },
                    "fontColorSecondary": {
                        "solid": {
                            "color": "#0e1622"
                        }
                    },
                    "backColorSecondary": {
                        "solid": {
                            "color": "#F4F4F4"
                        }
                    },
                    "bandedRowHeaders": true,
                    "valuesOnRow": true,
                    "wordWrap": true,
                    "fontFamily": "Calibri",
                    "fontSize": 10
                }
            ],
            "subTotals": [
                {
                    "rowSubtotals": true,
                    "columnSubtotals": true,
                    "fontColor": {
                        "solid": {
                            "color": "#0e1622"
                        }
                    },
                    "fontFamily": "Calibri",
                    "fontSize": 10,
                    "applyToHeaders": true,
                    "applyToLabels": true
                }
            ],
            "rowTotal": [
                {
                    "fontColor": {
                        "solid": {
                            "color": "#0e1622"
                        }
                    },
                    "fontFamily": "Calibri",
                    "fontSize": 10,
                    "applyToHeaders": true
                }
            ],
            "columnTotal": [
                {
                    "fontColor": {
                        "solid": {
                            "color": "#0e1622"
                        }
                    },
                    "fontFamily": "Calibri",
                    "applyToHeaders": true,
                    "fontSize": 10
                }
            ],         
            "totals": [
                {
                    "fontColor": {
                        "solid": {
                            "color": "#0e1622"
                        }
                    },
                    "fontFamily": "Calibri",
                    "bold": true,
                    "applyToHeaders": true,
                    "applyToLabels": true,
                    "fontSize": 10
                }
            ]
        }
    }
}

 

 

When I load it into the report there's these two that don't do what I want (see attached pics):
- Column grand total needs to be Calibri 10, apply to labels true
- Row subtotals needs to also be Calibri 10,apply to labels true

 

I also included a pic of current vs desired. Please let me know what I did wrong!

  • Anonymous's avatar
    Anonymous
    2 years ago

    Ok, solved it myself after trying different stuff. I had to go to the subtotals section and define font for rowsubtotals and columnsubtotals separately., like so: 

    "rowSubtotals": true,
    "fontFamilyrowSubtotals": "Calibri",
    "fontSizerowSubtotals": 10,
    "columnSubtotals": true,
    "fontFamilycolumnSubtotals": "Calibri",
    "fontSizecolumnSubtotals": 10

     

    This is the whole solution:

     

    {
    	"name": "MatrixTemplate",
    	"visualStyles": {
    		"pivotTable": {
    			"*": {
    					"stylePreset":[{
                        			"name":"Default"
                    			}],
    					"grid": [{
    					"gridVertical": false,
    					"gridVerticalColor": { "solid": { "color": "#F4F4F4"}},
    					"gridVerticalWeight": 2,
    					"gridHorizontal": false,
    					"gridHorizontalColor": { "solid": { "color": "#F4F4F4"}},
    					"gridHorizontalWeight": 2,
    					"rowPadding": 4,
    					"outlineColor": { "solid": { "color": "#F4F4F4"}},
    					"outlineWeight": 2,
    					"textSize": 10,
    					"imageHeight": 100
    				}],
    				"columnHeaders": [{
    					"fontColor": { "solid": { "color": "#ffffff"}},
    					"backColor": { "solid": { "color": "#142640"}},
    					"autoSizeColumnWidth": true,
    					"fontFamily": "Calibri",
    					"fontSize": 10,
                        "bold": true,
    					"alignment": "Left",
    					"titleAlignment": "Left",
    					"urlIcon": true,
    					"wordWrap": true
    				}],
    				"rowHeaders": [{
    					"fontColor": { "solid": { "color": "#0e1622"}},
    					"backColor": { "solid": { "color": "#ffffff"}},
    					"outline": "Frame",
                        "outlineColor": { "solid": { "color": "#142640"}},
                        "outlineWeight": 1,
    					"stepped": true,
    					"steppedLayoutIndentation": 12,					
    					"urlIcon": true,
    					"wordWrap": true,
    					"fontFamily": "Calibri",
    					"fontSize": 10,
    					"alignment": "Left", 
    					"showExpandCollapseButtons":true
    				}],
    				"values": [{
    					"fontColorPrimary": { "solid": { "color": "#0e1622"}},
    					"backColorPrimary": { "solid": { "color": "#ffffff"}},
    					"fontColorSecondary": { "solid": { "color": "#0e1622"}},
    					"backColorSecondary": { "solid": { "color": "#F4F4F4"}},
    					"bandedRowHeaders": true,
    					"valuesOnRow": true,
    					"outline": "Frame",
    					"urlIcon": true,
    					"wordWrap": true,
    					"fontFamily": "Calibri",
    					"fontSize": 10
    				}],
    				"subTotals": [{
                        "rowSubtotals": true,
                        "fontFamilyrowSubtotals": "Calibri",
    					"fontSizerowSubtotals": 10,
                        "columnSubtotals": true,
                        "fontFamilycolumnSubtotals": "Calibri",
    					"fontSizecolumnSubtotals": 10,
    					"applyToHeaders": true,
    					"perRowLevel": true,
    					"perColumnLevel": true					
    				}]
    			}
    		}
    	}
    }

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Ok, solved it myself after trying different stuff. I had to go to the subtotals section and define font for rowsubtotals and columnsubtotals separately., like so: 

    "rowSubtotals": true,
    "fontFamilyrowSubtotals": "Calibri",
    "fontSizerowSubtotals": 10,
    "columnSubtotals": true,
    "fontFamilycolumnSubtotals": "Calibri",
    "fontSizecolumnSubtotals": 10

     

    This is the whole solution:

     

    {
    	"name": "MatrixTemplate",
    	"visualStyles": {
    		"pivotTable": {
    			"*": {
    					"stylePreset":[{
                        			"name":"Default"
                    			}],
    					"grid": [{
    					"gridVertical": false,
    					"gridVerticalColor": { "solid": { "color": "#F4F4F4"}},
    					"gridVerticalWeight": 2,
    					"gridHorizontal": false,
    					"gridHorizontalColor": { "solid": { "color": "#F4F4F4"}},
    					"gridHorizontalWeight": 2,
    					"rowPadding": 4,
    					"outlineColor": { "solid": { "color": "#F4F4F4"}},
    					"outlineWeight": 2,
    					"textSize": 10,
    					"imageHeight": 100
    				}],
    				"columnHeaders": [{
    					"fontColor": { "solid": { "color": "#ffffff"}},
    					"backColor": { "solid": { "color": "#142640"}},
    					"autoSizeColumnWidth": true,
    					"fontFamily": "Calibri",
    					"fontSize": 10,
                        "bold": true,
    					"alignment": "Left",
    					"titleAlignment": "Left",
    					"urlIcon": true,
    					"wordWrap": true
    				}],
    				"rowHeaders": [{
    					"fontColor": { "solid": { "color": "#0e1622"}},
    					"backColor": { "solid": { "color": "#ffffff"}},
    					"outline": "Frame",
                        "outlineColor": { "solid": { "color": "#142640"}},
                        "outlineWeight": 1,
    					"stepped": true,
    					"steppedLayoutIndentation": 12,					
    					"urlIcon": true,
    					"wordWrap": true,
    					"fontFamily": "Calibri",
    					"fontSize": 10,
    					"alignment": "Left", 
    					"showExpandCollapseButtons":true
    				}],
    				"values": [{
    					"fontColorPrimary": { "solid": { "color": "#0e1622"}},
    					"backColorPrimary": { "solid": { "color": "#ffffff"}},
    					"fontColorSecondary": { "solid": { "color": "#0e1622"}},
    					"backColorSecondary": { "solid": { "color": "#F4F4F4"}},
    					"bandedRowHeaders": true,
    					"valuesOnRow": true,
    					"outline": "Frame",
    					"urlIcon": true,
    					"wordWrap": true,
    					"fontFamily": "Calibri",
    					"fontSize": 10
    				}],
    				"subTotals": [{
                        "rowSubtotals": true,
                        "fontFamilyrowSubtotals": "Calibri",
    					"fontSizerowSubtotals": 10,
                        "columnSubtotals": true,
                        "fontFamilycolumnSubtotals": "Calibri",
    					"fontSizecolumnSubtotals": 10,
    					"applyToHeaders": true,
    					"perRowLevel": true,
    					"perColumnLevel": true					
    				}]
    			}
    		}
    	}
    }

     

  • Hello,

    Could you please confirm whether there is an up-to-date solution for Microsoft Fabric / the current Power BI version?

    I tried the approach described above for formatting Matrix / Pivot Table row subtotals through the JSON report theme, but it does not work in my environment.

    I tested it after importing the theme and creating a new matrix visual, not only on an existing visual.

    The following settings are not applied to Row subtotals / Values:

    fontFamilyrowSubtotals = Calibri
    fontSizerowSubtotals = 10
    boldrowSubtotals = false

    I also tried the standard subTotals properties:

    fontFamilyrowSubtotals = Calibri
    fontSizerowSubtotals = 10
    boldrowSubtotals = false

    However, after applying the theme and using Reset to default, the font for Row subtotals / Values still remains Segoe UI.

    Could you please advise whether the property names have changed in the current Microsoft Fabric / Power BI version, or whether this setting is currently not supported through JSON themes?

    Thank you.