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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
jeroenterheerdt
Microsoft Employee
Microsoft Employee

Share your thoughts on visual calculations (preview)

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!

178 REPLIES 178
rubenjoosen
New Member

Hi

 

The combination of dimensional fieldparameters and visual calculations seema to look fine at first sight. 
It looks like its not possible with field parameters that contain a list of measures, because in the visual calculation you hardcode the current measure.

Is it possible someway or is it coming later to include the "current selected measure" and use it in the visual calculation. 
Similar to the Selectedmeasure() function in calculation groups. 

Would be quite powerful i would imagine. 

hi @rubenjoosen thanks for the feedback. You are absolutely right and we are thinking about how to resolve this, but then not just for visual calculations but for measures the same. It will take a while though, it's not easy.

wcarter
Advocate I
Advocate I

I was wondering, is there any update on when conditional formatting will be available to use with Visual Calculations? I have a table with conditional formatting that is pretty memory intensive and often fails to render on the PBI service, being able to apply that formatting to the completed calculation instead would be a massive boon to report performance for me.

hello @wcarter thanks for the feedback! Conditional formatting is on it's way, hopefully by the end of the year or early next year!

mattd1
Regular Visitor

Tried to use Vis Calc on a matrix where I had switched axis. I'm running Version: 2.137.952.0 64-bit (October 2024)

 

This worked fine in one PBI file but then copy paste the visual with the calc to another (pointing at same semantic model) went all screwy. If I remove the calc the switched matrix looks fine. If I add the calc back it doesn't ... 

 

Note the columns are a Calculation Group table.

 

mattd1_0-1730385911763.png

Above is visual without calculation. If I now add a subtraction of Port - Bench ... it goes like this:

 

mattd1_2-1730386037663.png

 

The calculation is working. This file is a PBIP. I have tried copying to another PBIX too ...

 

Here in another (PBIX) file - all working (note I called the field __ in this one rather than "Relative" as above):

 

mattd1_3-1730386113821.png

 

When I copy paste this to a brand new unsaved file:

 

mattd1_4-1730386285700.png

 

It's all flattened out. If I remove the Calculation I get this:

 

mattd1_5-1730386315251.png

 

 

Help?

@mattd1what's the calculation you added? It's unclear to me in the screenshots which calculation you are adding. Please post the DAX.

I mentioned subtraction of Port - Bench in the note as I didn't think the calc was relevant (it works in one file but not in another). But for info:

 

[Portfolio (EUR)] - [Benchmark (EUR)]

 

 

I am assuming Portfolio (EUR) and BenchMark (EUR) are measures, correct? I was a bit confused since you also used the same names in first column of your tables so I didn't know if you were trying to refer to the row "headers" in your calculation. 

 

I have been trying to reproduce the problem, but everything worked fine after copy/pasting, with or without adding a calculation group into the mix. Is there any chance you would be able to share the PBIX with me?

Hi thanks a lot for responding.

 

It's funny how it worked in one file but not in another (even creating from scratch in the other file).

 

Yes, those names (Portfolio (EUR) and Benchmark (EUR)) are the renamed measures on the visual.

 

I am wondering if there is some file version thing going on - maybe the first time I did it using September release, then one of my colleagues prepared a separate PBIP file for our project perhaps using later release (or earlier) ...

 

Sharing the PBIX might be challenging from our development environment. Very sensitive client data, and model separated from report files etc. Could do an online Teams call or something and look over it together if any use to you?

 

Thanks for taking time to help.

I'll send you a direct message to exchange details so we can do some screensharing.

Joseph_Fadero12
Regular Visitor

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

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

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.

ToddChitt_0-1728426367196.png

 

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:

ToddChitt_1-1728426535903.png

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!




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





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. 

 

ToddChitt_0-1728425311556.png

 

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]). 

 

ToddChitt_2-1728425759224.png

 

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?

 




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





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
Resolver III
Resolver III

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

kleigh_0-1727440294222.png


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.

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.

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:

kleigh_3-1728315319665.png
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.

thanks a lot @kleigh!

Idrissshatila
Super User
Super User

@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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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