Thousands Separator Option in Reports with AS DB Data Source

[cb1017]

Hello,

I have a stacked bar chart visual wherein a thousands separator is working as expected within invididual bar values, but I cannot get the thousands separator to work in a "TOTAL" line within the same visual. Below is a screenshot which demonstrates the issue:

I was able to find a thousands separator option within the measure I am using for this TOTAL line, and you'll see in the screenshot below that it is currently disabled. However, I cannot make any changes; all of these options are grayed out. I was told by one of our developers that there is no way to enable this option when using an AS DB as the data source, which I am using in this particular report. I figured I'd consult with this group to see if there may be a way to do this that we're just not aware of. It should be noted that we are currently using PBIRS vs. PBI Service; not sure if that might make a difference here. Thank you for your help!

[OwenAuger]

Hi @cb1017 

As you've seen, it isn't possible to override number format settings for a measure defined in a source Analysis Services database.

My suggestions:

• Ideally, have the number format updated in the source Analysis Services database.
• If that's not possible or can't be done soon enough, you could create a report-level measure in Power BI Desktop that is set equal to the original measure, and you will be able to control its number format settings. Then use that on the visual instead.

Not sure if it's related to your issue, but I have seen problems with calculation groups, where if a calculation group is applied, the format string isn't applied correctly to total labels.

Regards,

Owen

[cb1017]

Thank you for prompt reply, @OwenAuger ! 

Unfortunately, it seems that these suggestions will not work. We already have the measure formatted correctly in the AS DB since it correctly embeds commas in the numbers shown in the graph, just not in the line totals shown in the graph -- although hovering the cursor over any given line total does show the comma separators correctly (see first screenshot below).

Creating a report-level measure is also not possible because that functionality is greyed out when using an OLAP AS DB data source in Desktop (see additional screenshot below). Any other suggestions?

[OwenAuger]

Ah I see, this is Analysis Services Multidimensional, so report-level measures aren't possible.

I will have to defer to someone more familiar with Multidimensional and how it impacts format strings in Power BI, so sorry, I'm not much help here.

On the face of it, certainly looks buggy on how Power BI is handling the number formats.

I have seen odd behaviour involving Analysis Services Tabular or Power BI datasets when calculation groups are involved, but that won't apply here.

[cb1017]

Thanks for your insight, @OwenAuger ! It is certainly strange behavior. We'll see if anyone else has suggestions.