fredrikheden / summarytable

Summary Table Custom Visual for Power BI

Summary Table Power BI Custom Visual

The summary table is a custom visual that allows you to define tables in Power BI with summary rows and custom styling. The most common scenario for using this custom visual is to create the an income statement report.

• Change Log

The above image shows a summary table with an income statement with two summary levels.

The abone image shows a summary table with an income statement with yellow highlighting.

The JSON structure

{
	"columns": [...],
	"rows": [...],
	"headerRow": {...},
	"displayAllRows": true/false,
	"additionalWidth": 5, // Obsolete from version 2.0.0
	"reusableCSS" : [
		{
			"key": "#blackbackground#",
			"value": "background-color:#000;color:#fff"
		}
	],
	"masterHeader": {
		"title": "Master header of table",
		"headerStyle": "background-color:#000000;color:#fff;padding:5px",
		"borderStyle": "border:1px solid #000000"
	},
	"culture": "en-US",
	"alternatingRowStyle": "background-color:#F8FaFd"
}

• displayAllRows - if this property is set to true all visible header rows will be shown, even if they contain no data.
• additionalWidth - additional width to add to the containing table. This property needs to be set is using borders on the columns. Add the total amount of horizontal border width to this cell.
• reusableCSS - a way to define a css string that is reusable in all the style properties.
• masterHeader - this property can be used to create a border and master header for the whole table. If no header/border are to be used, skip this property.
• culture - this property is currently not working due to a bug in the Power BI Custom Visual API.
• alternatingRowStyle - optional style property that will be applied to every other row is used.

Define columns - columns

Example

{
	"headerStyle": "text-align:left",
	"rowStyle": "text-align:left",
	"width": 260,
	"type": "RowHeader",
	"refName": "[AccountGroup]", 
	"title": "Amounts in k$",
	"calculationFormula": "", 
	"format": "",
	"hidden": false,
	"styeByMeasure": "[AccountGroupStyle]"
}

Properties

• headerStyle - the css style of the header of the column.
• rowStyle - the css style of the row items of the column.
• width - the width in pixels of the column.
• type - the type of column. Valid values are RowHeader, Data and Calculation. RowHeader is used for the first column that contains the header for each row. Data is used for a column that is bound to a measure. Calculation is used when a specific calculation should be applied at render-time.
• refName - the name of the data bound measure/attribute. Only applicable when using RowHeader and Calculcation type.
• title - the displayed title of the column. References to measures can be done by using (). To reference a measure called year can be made by writing "eval([Year])". If the measure returns a string you need to use the following syntax: eval(\"[MyStringMeasure]\").
• calculationFormula - the formula that should be applied when using the Calculation type. E.g. "[Savings SEK]/[Spend SEK]". Measures are referenced betweeen brackets ([]). Any formula and operator that can be evaluated using javaScript kan be used.
• format - the formatting that should be applied when rendered. E.g. "#,0" and "0.0 %;-0.0 %;0.0 %".
• hidden - if this property is set to true, the column will not be displayed (can be useful when rows are referencing columns directly).
• styeByMeasure - this optional property can be used to set the style of the current column dynamically from the DAX measure. Note that there has to be a (hidden) column defined to encapsulates the referenced measure before it can be used. This can typically be used to color code KPI measures.

Define rows - rows

Example

{
	"title": "Total revenues",
	"formula": "[Operating revenues]+[Other revenues]",
	"rowStyle": "font-weight:bold;font-size:small;",
	"visible": true,
	"cellRowHeaderStyle": "",
	"cellRowDataStyle": "",
	"format": "",
	"hideForColumns": ["[Amount]"],
	"directColumnRef": [
		{
				"columnRefName": "[Budget]",
				"columnReplaceRefName": "[Custom measure %]"
		}
	]
}

Properties

• title - the displayed title of the row.
• formula - the calculation formula of the row. Either a direct reference to another row header row is used, e.g. "[Operating revenues]", or a summary formula is expressed, e.g. "[Operating revenues]+[Other revenues]". Any operator is allowed (from v1.4.0). For ranges the :: operator i used, e.g. "[100]::[200]". It is allowed to reference calculated rows is they are created before this row.
• rowStyle - the css style of the whole row container.
• visible - true or false, indicatng whether the row should be displayed or not.
• cellRowHeaderStyle - the css style of the row header (the first column).
• cellRowDataStyle - the css style of the row data columns header (all columns except the first one).
• format - optional formatting that should be applied to the whole row when rendered. E.g. "#,0" and "0.0 %;-0.0 %;0.0 %".
• hideForColumns - if the refName of the columns is listed here the column will display a blank value.
• directColumnRef - optional property that, when used, will replace a specific column value with another column value. Note that the (hidden) column has to be defined before it can be used.

Define header row style - headerRow

Example

"headerRow": {
	"rowStyle": "background-color:#aaa"
}

Properties

• rowStyle - the css style of the whole row container for the header columns.

Known issues & limitations

• If a row calculation is used in combination with a column calculation, the row calculation is done before the column calculation.
• Hover-effects are not supported.
• Expression-based css styles are not supported (e.g. settings a background color based on an expression).
• Cross-filtering other visuals (by clicking on a row) is not supported.
• If a directColRef (replace a value) is used, subsequent references to the replaced value is not supported.

summarytable's Issues

header columns that span other columns

I just downloaded your pbi visual. Great job! I'm a BI guy, not a json/css guy but found your examples spot on and easy to reuse. Excellent for my finance work.

Is there a cool way to accomplish what i'm about to describe in json/css?

Using the Matrix visualization as an example. I'd have rows, columns and values. Is there any way to fake this? I don't need drill down or anything, this is strictly for formatting purposes.

I currently use rows and columns in your pbiviz, right? So is there a way to accomplish displaying, say, months as a column and then displaying the columns I use now as 'values'? End result would have Budget, Plan, Actuals for each month.

Issue: Division Row Calculation

Hi Fredrik,
Firstly, thanks for the incredible work on this visual!!
I am experiencing some issues with the row calculations - where division with the / operator is adding both fields. I see the same issue in your sample report (Row Calculations - Row calculation with division and custom formula). I've tried upgrading & downgrading Power Bi aswell as the visual to get this to work but to no avail, is there something I am missing on my end that could be causing this or is this a known issue?
Thanks again for all the great work ! 👍 🥇

Row Number Formatting Issues

All of my row number formatting disappeared with the latest update, and the table doesn't reflect the changes when I add it back in the code view (e.g. "format": "0.0 %;-0.0 %"). Is there a different way to determine individual row number formats in the new version? Thanks!!

Accounting format

Is there a simple style script to allow negative numbers to display within a bracket and in red similar to DAX Format-> FORMAT([Measure],"#,##0.0;(#,##0.0)"). Thank you Bruce

Measures and calculations

It would be great if you could allow the possibility to show different measures in the same column and allow calculations between different measures in the same column. It will help building p&l/cash flow reports with the benefits of DAX.

row formula

Hi,
Not an issue, but a request. Allow / on row formula so we can calculate percent from different rows.
Thanks.

Blank values does not add up

I am very new to the code in this visual, is there a code that can solve my problem?

I am trying to use your Summary Table and it has solved almost all the requests I have, but I have a problem with the formulas if the "values" I total in a formula is blank then the result is blank even if the total should be a value.

simplified example:

[Net Sales] = 100
[Cogs] = 50
[Contribution I]= 50
[Other costs] = blank or no values
Contribution II = blank - though it should result in 50

Indirect Title Reference: "eval([Measure])" does not seem to work

Hi Fredrik - Is this function still working? I tried it on the latest official and beta releases, it does not seem to call the measure I have in the model. It only shows the text string between the "". Love the tool, thank you for your help.

Row-level security (RLS) breaks visual

Hi Fredrik
Thank you for the excellent work on the custom visual, it does contribute much to the capabilities of Power BI as a product.
I have been using it in a project for Income Statements, but when attempting to implement RLS, the visual breaks. RLS filters out some cost centres of the business and as a result some of the row items are also filtered out. I noticed that when I remove the reference to these row items from the JSON, the visual starts working again. I guess the reference to a non-existing data item causes the visual to break? Is there a solution to this?
Thank you.

Data to columns

Hi!

I am a complete newby in writing any code at all.
I understand, how to format things in your visual, but I have no Idea how to split data into columns:

1. I have a data table of three columns: [Account_name], [Data], [Amount]
2. Respectively I add them all as fields to the visual
3. After that I have the following columns code (see below)
4. What I want is to display [Amount] depending on the [Date]. I thought is should be done via "CalculationFormula" in the "columns" part, but I failed all the attempts.

Thank you!

{
"headerStyle": "border-bottom:1px;border-bottom-color:#eee;border-bottom-style:solid",
"rowStyle": "text-align:left",
"width": 150,
"type": "RowHeader",
"refName": "[Account_name]",
"title": "",
"calculationFormula": "",
"format": ""
},
{
"headerStyle": "border-bottom:1px;border-bottom-color:#eee;border-bottom-style:solid",
"rowStyle": "",
"width": 150,
"type": "Calculation",
"refName": "[Amount]",
"title": "2017",
"calculationFormula": "[Amount]",
"format": "#,#"
},
{
"headerStyle": "border-bottom:1px;border-bottom-color:#eee;border-bottom-style:solid",
"rowStyle": "",
"width": 150,
"type": "Calculation",
"refName": "[Amount]",
"title": "2018",
"calculationFormula": "[Amount]",
"format": "#,#"
}
]

Row Formatting

Hi,

Love this visual! I'm not an expert with CSS, though I am learning just by playing around with this visual. However, I have not been able to figure out how you achieved the various row shadings in your examples. Can you advise on the parameters needed for shading specific rows in a column? I'm trying to create an alternating background color.

Many thanks!

Trey

Measure names with parentheses

Using this visual for a P&L statement, we have a measure named 'NET INCOME (LOSS)' which isn't pulling through. All of the other measures are pulling correctly and this follows the same syntax as the others but I'm guessing due to the parenthesis this is getting dropped. I've double checked the names match and swapping the measure used in the formula line pulls correctly after being switched to a measure that doesn't have parenthesis. Are parenthesis supported in measure names?

    {
        "title": "NET MARGIN",
        "formula": "[NET INCOME (LOSS)]",
        "rowStyle": "",
        "visible": true,
        "cellRowHeaderStyle": "font-weight: bold; padding-top: 2px; border-top-color: #E6E6E6; border-top-style: solid; border-top-width: 1px",
        "cellRowDataStyle": "padding-top: 2px; border-top-color: #E6E6E6; border-top-style: solid; border-top-width: 1px"
    },

Blank Values

I am trying to use your Summary Table and it has solved almost all the requests I have, but I have a problem with the formulas if the "values" I total in a formula is blank then the result is blank even if the total should be a value.

simplified example:

[Net Sales] = 100
[Cogs] = 50
[Contribution I]= 50
[Other costs] = blank or no values
Contribution II = blank - though it should result in 50

Border-bottom-style:dotted is getting crazy

i created a border bottom style as dottet for each colum.
for each cloum the first dot is solid black and the dotts after are grey.
is this a known issue?

Help formatting row numbers.

I'm having an issue changing "," for "." in thousands separator. No matter how I format, through modeling options, setting the format for the whole measure, or through the row/column properties "format", setting "#.0" for thousands and comma for decimals.
Don't know if it is a known issue, or just me doing something wrong. Anyone else had this problem and solved?

Power BI support version

Can you please let me know which version of Power BI that can support the Summary Table custom visual?

Change Column to Row

Is there any way to change column and row?

Error: SUM of rows

I have some issues, where when summing certain rows in my Summary Table using JSON it somehow uses the wrong values. Eg. I have a row 'A' with a value of 19,231,565 which is shown in the report and this is the correct value, but the Subtotal using this row doesn't add up. When editing the JSON code I can see in the preview below the editor, that this number is changed to 38,463,130 (exactly double), and this does make the subtotal add up, however it is completely wrong.

I updated Power BI Desktop today which I at first thought was the culprit, however, downgrading to a previous version (Jan 2019) didn't fix the issue. This worked perfectly fine a week ago, so I don't know if an update you pushed has caused a bug, or if I'm doing something wrong.

Edit: I found out that if for the first column I make a row 'A', if the 'group' is also called 'A' this somehow doubles the value, at least for the calculations. Instead I called the row 'A:' (colon added) and now it works fine.

Not all rows generated automatically

Hi,

In an example with 17 row, only 7 row are generated in the Generate template from field list.
See attached screendump.
Which is fine - I just wondered if that was the desired result.
I just want to know how to instruct end-users.

Thanks

dynamic styling

It does not work (wrong behavior - budget is actually grater then actual but actual is green) in case of added summary rows ( view your added example pbix).

directColumnRef double condition

Hello everyone!

Somebody knows how I can write a double condition if I want to replace in the same row two different values in two different columns?

If I write one condition and add another one, it takes only the last one and not two at once...

Thanks a lot!

This is my code. So it does not work if I write an additional condition under "directColumnRef" :(

{
"title": "Sales",
"formula": "[I. Sales]",
"rowStyle": "",
"visible": true,
"cellRowHeaderStyle": "",
"cellRowDataStyle": "",
"directColumnRef": [
{
"columnRefName": "[MA_2021]",
"columnReplaceRefName": "[Sales 2021]"
}
]

Ability to conditionally format text

Does this visual allow the ability to define colored text or text formatting based on conditions?

For example I would like to have a a number be Red if it is below zero, and Green if above zero.

Only adding and subtracting working on row formulas?

HI,

I can only get adding and subtracting to work in row formulas.

I am trying to do division for ex. Gross Profit / Net Sales.
I only adds upp the numbers from the row fields.
Is it a bug, or am I doing it wrong?

Thanks.

directColumnRef for another calculation

Hi, when a value is replaced through directColumnRef, then subsequent use of that row for another calculation is causing column value to shift. In attached screenshot, green circle is a value replaced through 'directColumnRef' and red circle is when that row is used for calculation later - column values appear shifted.

Freeze Row and Column Headers

Hi Frederik,

Is it possible to freeze the column headers or the row labels? I have a large table and when I scroll down or to the right I lose the headers and row labels.

thanks,

Mike

Cannot export visual as a PDF in the Power BI Service

Fantastic work, @fredrikheden. The SummaryTable allows me to produce a variety of pixel-perfect financial statements.

One issue: I can export a PDF containing a SummaryTable visual in Power BI Desktop just fine. But when I try to export it through the Power BI service on app.powerbi.com, the visual does not render and there's a warning that says, "This visual does not support exporting."

Any clue as to what might be happening?

Right and left border formatting

Hi,

I can not get the right and left border style formatting to work properly. When I try this, my last data column jumps to a new line - does not matter how many data columns, the last one always jumps to next line as per picture below.

Example from your sample file where I have changed the "border-top-style" property to "border-right-style":

"headerStyle": "border-right:1px;border-right-color:#aaa;border-right-style:solid;

Divide calculations in column formulas handling zero values in numerator or denominator

I'd like to avoid the "-Infinity", "+Infinity" and the "NaN" values in the table resulting from dividing a zero value or dividing by a zero value. I have tried to implement a conditional calculation in your last downloadable version, but cannot get it to work. Syntax like

"[Actual YTD] >0 ? [EBITA YTD]/[Actual YTD] : 0"

but it does not work. Are these sorts of calculations possible in your latest GitHub version? Or is it just that my syntax is wrong?

Rows became enourmously wide with "additionalWidth"

Hi! I'm using "additionalWidth" to set borders to my cells, with its value equals "1" in JSON structure. But my rows became enourmously wide, filled with colored background. I suppose, additionalWidth property adds 1 full width of all table. But, how can I add only few pixels to avoid cells being moved when I`m using borders? Because I tried to set decimal value, but it doesn't work at all.

Format font color

Hi,
Not an issue as such - but I can't get the font color to be formatted in for ex. the headerStyle tag.
Tried all sorts of syntax, but no effect.
Is it possible can you give an example of a working syntax?

Tnx
Lotta

Different format for thousand seperator/Comma

Is there a possibility to force another format for the thousand seperator and the comma? Currently the only option is the American one (1,000.00) would love to be able to use the european one (1.000,00)

Tutorial

Do you have any video teaching how to do it? Maybe I'm asking too much, but I'm not a programmer nor a native English speaker, which made things a bit complicated

Anyway, congratulations for the work done

Allow drillthrough

It would be great if you could drillthrough from this visualization to another page using a drillthru filter as you can do with some other visuals.

Drill down in rows

I have found this summarytable through Power BI marketplace. It is an aweome visual.
It fits with a lot of needs that can´t be found in other visuals of PBI.
I was wondering if it could be posible to do a drill down by rows as it is made in the matrix visual of Power BI, it will add a lot of value and posibilities to the summary table.

Thanks!

v1.4 Visual not working in IE 11

Thank you for including row calculations with any expression. I have been eagerly awaiting this functionality. I downloaded your new pbiviz and went to use it in my pbix file and it shows up blank IE11. Then, I used your sample file and imported the 1.4 visual into it. This shows up blank in IE11 but works in chrome and power bi desktop. I have attached two screenshots to showcase the error.

Power BI Desktop -

The left visual (I assume it is 1.3) and right visual (which is 1.4) both work in Desktop and Chrome.

IE 11-

You can see the left visual works, but the right visual is blank.

directColumnRef

Hi Frederik, I must say this is great visual and great help for generating P&L report. I am stuck at one point and hope you can help with. I am using directColumnRef to replace column value with other column in a calculated row. Is this correct approach and that is how it is intended to use? Do you have any sample report I can look at how this property can be used? I am using Version: 3.0.2 from PowerBI App Store.

Thanks.