"Total rows" in custom Power BI visualizations

Question

I have a question about creating the custom visualization in Power BI.

I want to implement a "total row" functionality which is available in the built-in matrix visualization. The main concept is to automatically sum-up every value and group it by the rows. This is how it's looks like on the matrix visualization:

But, to be honest, I don't know how to achieve this. I try different things but I can't receive this grouped values in the dataViews.

I tried to analyze the built-in matrix.ts code but it's quite different that the custom visualizations code. I found the customizeQuery method which set the subtotalType property to the rows and columns - I tried to add this in my code but I don't see any difference in the dataViews (I don't found the grouped value).

Currently my capabilities.dataViewMappings is set like this:

            dataViewMappings: [
            {
                conditions: [
                    { 'Rows': { max: 3 } }
                ],
                matrix: {
                    rows: {
                        for: { in: 'Rows' },
                    },
                    values: {
                        for: { in: 'Values' }
                    },
                },
            }
        ]

Does anyone know how we could achieve this "total row" functionality?

UPDATE 1

I already found the solution: when we implement the customizeQuery method (in the same way as the customizeQuery method in the matrix.ts code), and then add the reference to it in the powerbi.visuals.plugins.[visualisationName+visualisationAddDateEpoch].customizeQuery then it works as expected (I receive in the dataViews[0].matrix.row.root children elements that has the total values from row).

The only problem now is that I don't know exactly how to add correctly this reference to the customizeQuery method. For example the [visualisationName+visualisationAddDateEpoch] is Custom1451458639997, and I don't know what those number will be (I know only the name). I created the code in my visualisation constructor as below (and it's working):

    constructor() {
        var targetCustomizeQuery = this.constructor.customizeQuery;
        var name = this.constructor.name;

        for(pluginName in powerbi.visuals.plugins) {
            var patt = new RegExp(name + "[0-9]{13}");
            if(patt.test(pluginName)) {
                powerbi.visuals.plugins[pluginName].customizeQuery = targetCustomizeQuery;
                break;
            }
        }
    }

But in my opinion this code is very dirty and inelegant. I want to improve it - what is the correct way to tell the Power BI that we implement the custom customizeQuery method and it should use it?

UPDATE 2

Code from update 1 works only with the Power BI in the web browser (web based). On the Power BI Desktop the customizeQuery method isn't invoked. What is the correct way to tell the Power BI to use our custom customizeQuery method? In the code from PowerBI-visuals repository using PowerBIVisualPlayground we could declare it in the plugin.ts file (in the same way like the matrix visual is done):

export let matrix: IVisualPlugin = {
    name: 'matrix',
    watermarkKey: 'matrix',
    capabilities: capabilities.matrix,
    create: () => new Matrix(),
    customizeQuery: Matrix.customizeQuery,
    getSortableRoles: (visualSortableOptions?: VisualSortableOptions) => Matrix.getSortableRoles(),
};

But, in my opinion, from the Power BI Dev Tools we don't have access to add additional things to this part of code. Any ideas?

I think if you post this in the visuals Github repo as an issue you may get an answer.. — Markive

Lukasz P. Lukasz P. · Accepted Answer · 2016-01-22T15:35:34

It seems you're missing the columns mapping in your capabilities. Take a look at the matrix capabilities (also copied for reference below) and as a first step adopt that structure initially. The matrix calculates the intersection of rows and columns so without the columns in capabilities its doubtful you'll get what you want.

Secondly, in the matrix dataview passed to Update you'll get a 'DataViewMatrixNode' with isSubtotal: true Take a look at the unit tests for matrix to see the structure.

        dataViewMappings: [{
        conditions: [
            { 'Rows': { max: 0 }, 'Columns': { max: 0 }, 'Values': { min: 1 } },
            { 'Rows': { min: 1 }, 'Columns': { min: 0 }, 'Values': { min: 0 } },
            { 'Rows': { min: 0 }, 'Columns': { min: 1 }, 'Values': { min: 0 } }
        ],
        matrix: {
            rows: {
                for: { in: 'Rows' },
                /* Explicitly override the server data reduction to make it appropriate for matrix. */
                dataReductionAlgorithm: { window: { count: 500 } }
            },
            columns: {
                for: { in: 'Columns' },
                /* Explicitly override the server data reduction to make it appropriate for matrix. */
                dataReductionAlgorithm: { top: { count: 100 } }
            },
            values: {
                for: { in: 'Values' }
            }
        }
    }],

"Total rows" in custom Power BI visualizations

1 Answers