Share your thoughts on visual calculations (preview)

[jeroenterheerdt]

Hit "Reply" and let us know what you think of visual calculations! To learn more about this feature, please visit the blog post or our documentation.

Here are some areas that we'd like to hear about in particular:

• errors / issues you are experiencing
• functionality that is missing / does not work as you expected that is not listed as limitations in our documentation.
• suggestions on governance capabilities
• sorting behavior of visual and visual matrix

Thanks and we look forward to hearing your feedback!

[Joseph_Fadero12]

Pretty good feature, My only tiny issue is editing the visual calculation, normally visual calculation requires opening the visual calculations edit mode, which can be less convenient than editing a measure. Also addition of more template expressions would be good, like the Running sum we have, more template visual cals maybe good

[jeroenterheerdt]

Hi @Joseph_Fadero12 thanks for letting me know. I am curious what kind of templates you'd like to see added?

[ToddChitt]

I don't like that when using Visual Calculation, you change the Axis Sort on me!

Case in point. Consider the following simple example of two years broken down by months (24 values for YYYYMM). I create a Waterfall chart and add to it a Visual Calculation from a Template for "Versus previous". I then hide the original [Sum of Value] and the Waterfall looks correct. The data is sorted by the X axis. Life is good.

Now I simply drag and drop [Sum of Value] from the Y-axis field well to Tooltips. I have not even made it visible yet. And magically my Watefall chart takes on a whole new shape:

Power BI has taken it upon itself to SORT MY VISUAL WITHOUT ME ASKING IT TO DO SO.

I see this happening all over the place. STOP SORTING MY VISUALS!!! You are NOT helping. You are making extra work because now I need to A) re-assign the Sort Axis (to YYYYMM) like I had it before and B) re-assign the Sort Direction to ASCENDING!

[ToddChitt]

There is a bug, I beleive in how the Running Sum template works. Consider the following simple set of five dimensional values (Class A through E) and a SUM aggregation measure. 

Notice that the Classes are NOT in alphabetical order. The data is sorted, purposefully, by the Measure.

Now I add a Running Sum Visual Calculation. The data in the data table, along with the Visual Calculation, is correct in that the Running Sum number is accurate, but that grid does NOT match the table visual. It is sorted by Class, NOT by the Measure.

The [Running Sum] for Class E should be 1101 + 1097 = 2198, NOT 5138, based on the Sort Order of the table (by [Sum of Value]). 

What good is a Running Sum calculation if I cannot dictate the ORDER in which the values should be added sequentially?

In other words, how do I get the Data Table to be sorted by [Sum of Value] so that the subsequent Running Sum calculation is correct?

[jeroenterheerdt]

hi @ToddChitt, thanks for raising this. Currently you cannot influence the sorting behavior of the runningsum calculation or the visual matrix (what you call the data table), this is something we're working through.

You could also write a running sum using a window function (WINDOW) and influence the sort order using the ORDERBY() parameter function.

[kleigh]

Visual calcs could be used to offer an extension to the "Show Value As" option.

Alongside "Percent of grand total", it could offer "Percent of..." and allow any other numeric field in the row to be chosen.

I'm currently preparing a visual which is a set of percentages vs a baseline value. Previously, this required a lot of measures. Now, it can use visual calcs instead. This works, but the menu option would cut the clicks required to complete the task.

[jeroenterheerdt]

hi @kleigh thanks for the feedback! We are working on integrating visual calculations into this menu, but right now I am thinking about it as extending the options available with templates such as running sum / moving average. Since you open the "show value as" menu from a numerical value, the template would then be applied to that numerical value. If you open 'show value as' on a field named 'Sales' and chose 'running sum' we would add a visual calculation that is the running sum of Sales.

However, you seem to imply a different viewpoint on this, which is very interesting to me. Can you elaborate about this part of your message please: "Alongside "Percent of grand total", it could offer "Percent of..." and allow any other numeric field in the row to be chosen.". An example would be very helpful. Thanks in advance for your consideration.

[kleigh]

I don't think this conflicts with the idea of applying templates. It's just a template that would be useful for the cases where I'm already applying visual calcs.

We have columns that duplicates a boolean, except showing 1 for true and 0 for false. This would by itself show a count where a certain monitoring code has been set. For this visual, instead of a raw total, they are shown as a % of a baseline field. This is effectively the overall total with no filter applied, but since it's just another column it's not seen as being the total.

The visual calc is for example:

DAM 002% = DIVIDE([10 DAM 002: Procured], [Participation], 0)

And our result:

I've unhidden the count column here as an example, normally this visual hides them.

There are a few other visuals I have where there are two measures, the counting measure, then the same thing which is a percent of another measure. My general preference is to replace measures that are simple calculations (like a division) and used for one visual with visual calcs.

[jeroenterheerdt]

thanks a lot @kleigh!

[Idrissshatila]

@VijayChinna , @swixglider  @Frankie_Vannini  @gregst 

and for all people that were asking on a way to format values in visual calculations, in the August Update it was added as an option that is visual level formatting

you can check these resources to know more about it and how to perform it
https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-custom-format-strings

https://youtu.be/a6lI_B58wqQ?si=q8rdPO8xHI6gtlel

https://youtu.be/w1a55ujmEm4?si=xSJPaiYmQfuZQsD5

[jeroenterheerdt]

yep, and more is coming 🙂

[VijayChinna]

I'm trying to use it with a "Field Paramater" but it doesn't seem to work.
No options for formatting, ex: if we divide 2 measures for percentages to show on line chart as percentange (64.6%) or on tooltip is not possible

[jeroenterheerdt]

thanks for trying this out! Field parameters are listed as a limitation in our docs so they don't work yet with visual calculations.

[swixglider]

Thanks! That's a great new feature.

I'm trying to use it with a "Field Paramater" but it doesn't seem to work.

The option "New calculation" dissapear as soon as you add a field parameter to the table. Will it work in the near future? 

[jeroenterheerdt]

thanks for trying this out! Field parameters are listed as a limitation in our docs so they don't work yet with visual calculations.

[gregst]

Hello,

I've got an issue.

When I'm using fx to add the RUNNINGSUM I got the message below.
It referes to a column with the weeks. 
Whatever the measures I got this error

My table is quite simple

[jeroenterheerdt]

first of all, thank you for trying out visual calculations and I am sorry you weren't successful the first time around. I believe I understand what is going on here: the [Field] in the template is the numerical field you want to sum, so that's Snapshot in your case. RUNNINGSUM([Snapshot]) should work. Please try it and let me know if it works as expected. thanks again!

[gregst]

Hello
The error message is coming at the beginning, meaning when I click on "New Calculation" the error message is popping up.

The table is quite simple, I have the week and for this example I only put the measure Snapshot = 1

with a format as Whole number.

Even if I continue and create Calculation = RUNNINGSUM([Snapshot]) the screen stay with the error message.

The calendar I use to get the Week is linked with other table in my model. Could be the problem ?

I did several try and it seems the issue is coming only if I use the table "Calendar" 😞

[Frankie_Vannini]

Hi there

I've got a small vertical aligment issue the minute I use a unichar icon to display a negative or positive icon related to the value, waiting for conditional formatting option. This happens only using DIN Font bigger than 18 points

In essence I use this code for the visual calculation:

YoY % = 

VAR Curr = [ITEM]

VAR Prev = PREVIOUS([ITEM],COLUMNS)

VAR Result = DIVIDE(Curr-Prev,Prev)

VAR Color_Icon = if (Result >=0, UNICHAR(128994),UNICHAR(128308))

RETURN

if( Result = BLANK(), 

BLANK(),

CONCATENATE(

Format ( Result, "0.0% ") , ColorIcon

    )

)

the result is this one:

 

so using font DIN 14 on the left no significant vertical misalignement, while using DIN 34 I have a vertical misalignement. No issues with other fonts

By the way, any suggestion to change numbers in green or red using DAX and/or HTML?

Thanks

F

[gregst]

Hello again,
I solved my problem. The naming of the column seems weird for the New Calculation.
If I rename my column Calendar[ST Week] with ST Week all is working well.
Is it possible the square bracket [ ] create the problem ?