Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dhavalu
Frequent Visitor

Table Dataview dosent get totals with it.

Hello,

As per https://github.com/Microsoft/PowerBI-visuals/wiki/DataView-Introduction Table DataView should carry totals within it.

But I tried both in desktop and web version but it dosent workout for me. This property is always undefined.

I want to get the totals for column (when user selects Average or SUM or Minimum or Maximun etc).

I also looked into the aggregates but it gives only min & max. Subtotal is always undefined.

 

I am using below capabilities

dataRoles: [{
displayName: 'Values',
name: 'Values',
kind: VisualDataRoleKind.GroupingOrMeasure
}],
dataViewMappings: [{
table: {
rows: {
for: { in: 'Values' },
dataReductionAlgorithm: { window: { count: 100000 } }
},
rowCount: { preferred: { min: 1 } }
},
}],

Example code:

public update(options: VisualUpdateOptions) {
options.dataViews[0].table.totals;
options.dataViews[0].table.columns[0].aggregates.min;
options.dataViews[0].table.columns[0].aggregates.max
options.dataViews[0].table.columns[0].aggregates.subtotal;
}

 

Any Ideas?

 

5 REPLIES 5
Goofr
Advocate IV
Advocate IV

Hello,

 

I' ve created a simple custom visual showing a grid, however I can't find how to make it show the totalrow.

Searching the internet I stumbled upon this post. It's from a while ago, maybe you guys have some more insights now?

 

Thanks in advance!

 

Frank

v-viig
Community Champion
Community Champion

The totals aren't supported by Power BI.

We'll propose this idea to Power BI Custom Visuals API team.

 

Ignat Vilesov,

Software Engineer

 

Microsoft Power BI Custom Visuals

pbicvsupport@microsoft.com

itayrom
Resolver II
Resolver II

Well, I have some disappointing news for you:

In order for the dataview to carry the totals object within it, you have to explicitly ask for it. It is done by implementing a "customizeQuery(options: CustomizeQueryOptions): void" function and setting it as your visual's IVisualPlugin implementation's customizeQuery property. The bad news is that in order to do so, you have to access an API that is blocked by the custom visuals sandboxing. I.e. once you export you visual and use it in Power BI Service, it won't be able to access its IVisualPlugin object.

 

If you'd like to give it a try, it can be done as follows:

 

1. Implement a customizeQuery() method(Example is a bit simplified)-

 

public static customizeQuery(options: CustomizeQueryOptions): void {
    var dataViewMapping = options.dataViewMappings[0];
    if (!dataViewMapping || !dataViewMapping.table || !dataViewMapping.metadata)
        return;
    
    var dataViewTableRows: data.CompiledDataViewListRoleMapping = <data.CompiledDataViewListRoleMapping>dataViewMapping.table.rows;
    (<data.CompiledDataViewRoleForMapping>dataViewTableRows.select[1]).for.in.subtotalType = data.CompiledSubtotalType.Before; // **(Read notes)**
}

Notes:

 

  • In this example I wrote ".select[1]" since in my DataViewMappings I mapped the rows using a DataViewListRoleMappingWithReduction object. In general, the structure is the same as how you mapped the dataview in your capabilities object.
  • Though recognized in the DevTools, the "data.CompiledSubtotalType" enum is not available for the visual when used in the PBI Service, so may have to write its numerical value instead(0 = None, 1 = Before, 2 = After).

2. In your visual's init() method, get its IVisualPlugin object and set its customizeQuery property to the function you created-

visualPluginFactory.create().getPlugin("YourVisual1464287088710").customizeQuery = YourVisual.customizeQuery;

Notes:

  • The getPlugin() method accepts the plugin's name, which, in the case of custom visuals, is the guid generated by the DevTools.
  • The are other ways to access the IVisualPlugin object via the visualPluginFactory, but that module itself is not accessible under the sandboxing.

3. Now, once your visual is initialized, the function will be invoked by the environment and the totals object will be delivered in the subsequent invocation of update().

 

A very thin silver lining is that if you'd donate your visual to the custom visuals gallery, it won't be sandboxed, since Microsoft reviews the code of donated visuals prior to putting them in the gallery.

 

Final notes:

  • The IVisualPlugin object of custom visuals is generated when you compile your visual and can be viewed in the javascript file inside your .pbiviz file.
  • If you manually modify that javascript file to set the customizeQuery property, you won't be able to upload it to PBI Service.
  • Since there is no documentation available regarding the above, there might be a slight chance that there are other ways to achieve this, maybe some of them are not blocked by the sandboxing. But I know of this method because I read some of the source code of the official visuals that's available in the custom-visuals github project, and it seems like this is the way it is done(With the difference that their IVisualPlugin objects are programatically created and not automatically generated when you compile them, so they don't need to access them in the init() method).
  • I would not mind being proven wrong on this issue.

Thanks a lot for the wonderful insight.

 

Glad I could be of assistance.

Also, I just posted an idea regarding this issue to Power BI Ideas. Here's the link, in case you'd like to give it your vote-

 

 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/14807241-provide-a-way-for-custom...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors