Visual Calculations in Power BI

⚡⚡⚡More information in the description below⚡⚡⚡
A first look at Visual Level Calculations in the February 2024 release of Power BI Desktop. Looks very promising, should simplify a lot of common DAX requirements.
⚠️ Formatting Update:
There's new formatting options as of August 2024
• New Power BI Number Fo...
For more format options see learn.microsof...
If you need to do further calculations on a field AND you want to display it formatted then you will need to create a duplicate unformatted column and hide the original non formatted column that is used in further calculations.
At this stage you also cannot sort by a measure value (the RUNNINGSUM doesn't respect the sort order of a visual if it's sorted by a measure) Instead you'd need to do something like this
RUNNING TOTAL by Units= SUMX(WINDOW(1, ABS, 0, REL, ROWS, ORDERBY([Units], ASC)), [Units])
The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. accessanalytic...
Did you know I've written a book "Power BI for the Excel Analyst"?
pbi.guide/book/
Connect with me
wyn.bio.link/

[@AccessAnalytic]

⚠️ Formatting Update: There's new formatting options as of August 2024 kzbin.info/www/bejne/l2fPepJ4ap2qp7Msi=t0ldp_zZUb5B06Oy Official Blog post powerbi.microsoft.com/en-us/blog/power-bi-february-2024-feature-summary/

[@FredericLEGUEN-Excel]

The Microsoft engineers have understood that DAX is not easy to learn and to understand. Finaly, they have simplify the way to build your measure (that's a good point) 👍

[@newsbandi2002]

Yes syntax is too hard to remember and writing context based calcs are cumbersome

[@dutchdatadude]

Surprise! Awesome video! And yes, the format thing you mentioned is a limitation, for now. There are many more that we will have in our docs and will work on as we progress.

[@AccessAnalytic]

Cheers

[@smanzoli]

How does it differ from QUICK MEASURES we already had in Power BI???

[@analyst42]

Wow, thanks for sharing, Wyn! This is an awesome addition. I really look forward to using this in my own work. I think it will make PBI development much faster in certain situations.

[@AccessAnalytic]

Absolutely !

[@sanchowitfurrows1561]

it is unbielivable, I am looking for a proper percent per category measure for a few days and checked your channlel just to distract a bit and here you show me solution! Thank you!

[@AccessAnalytic]

😀 glad I could help

[@arduiscane]

Great video, great enthusiasm. I was looking for something that would walk me through what this is all about and you did great. I was playing with this earlier to try and add calcs to visuals where i built outright measures to solve one calc for one place and this hits the spot. So many single use measures in my model adding so much confusion

[@AccessAnalytic]

Thanks for taking the time to leave a kind comment

[@roverre61]

Thanks a lot Wyn i'm always enjoy your way to share, with an incredible passion what you discovery, your knowledge, in such nice "simple" and awesome style..!!! :)

[@AccessAnalytic]

Thank you very much for the kind words. Truly appreciated

[@PSModelling]

Perfect, great to see Power BI gradually becoming more user friendly as it's not the easiest thing to pick up.

[@AccessAnalytic]

DAX and the data modelling is the hardest piece

[@arbazahmad7177]

Excellent update 👏.. .... Dax become easy with this new updates...

[@AccessAnalytic]

Yes this will be really useful

[@alano444]

@accessanalytics There is a formatting option, not for the new calculation, but for the visual itself. Just add your new calculation. Then select your visual and select Format Visual | Gereral | Data Format | Apply Settings To and and then select you calculation name and there is a formatting option there

[@AccessAnalytic]

Good call, I did a video on it here kzbin.info/www/bejne/l2fPepJ4ap2qp7Msi=t0ldp_zZUb5B06Oy I've added that to the pinned comment and description Thanks!

[@smanzoli]

How does it differ from QUICK MEASURES we already had in Power BI???

[@AccessAnalytic]

Quick measures was a shortcut to writing normal DAX. This is a new concept and functions where you can easily write dax that refer to the rows and columns of calculations within the visual itself. So much simpler to write certain formula that would have been many many lines of conceptually difficult dax in the past

[@zvikabar-kochva3641]

Great feature. It looks like Power BI is going the way of Tableau. This feature is will make PBI much easier to use, but, it will need lots of improvements before it will acquire Tableau's level of calculation options.

[@AccessAnalytic]

A good start, they’ll learn a lot from how this is used and what issues folks hit most often.

[@huwdavis3735]

This is a great little video, but assumes that you have the feature available. Probably best not to assume that everyone has Preview Features set to allow this function to appear.

[@AccessAnalytic]

Thanks, I do mention it has to be enabled at 6:27 and have a message pop up in bottom left at 00:12

[@tiruveedipradeepkumar1487]

Very Nice...Good Information...Thank you so much....

[@AccessAnalytic]

Thanks, I appreciate you taking the time to let me know you found it useful

[@barttitulaerexcelbart9400]

Thanks Wyn, I think this will make live easier for a start.

[@AccessAnalytic]

Yep, should simplify those overly complex edge cases

[@clairejsquibb]

Like the Add/Remove Decimal icons in Excel, I'm sure those row/column arrows are going to cause no end of confusion!

[@AccessAnalytic]

Ah yeah those decimal icons are confusing . I’m ok with the rows and columns. Same concept as excel rows and columns

[@RichardOliver]

As I was watching this, I thought the exact opposite and thought they'd be really useful as a prompt for users!

[@charitaa1218]

That’s an amazing feature,thanks for your valuable insights.

[@AccessAnalytic]

You’re welcome

[@zzota]

That's a really useful feature. Thanks for sharing.

[@AccessAnalytic]

You’re welcome

[@DavidWilsonNZ]

Very clearly explained. Thank you Wyn.

[@AccessAnalytic]

You’re welcome David

[@wasif002]

Awesome. Thank you for this!

[@AccessAnalytic]

You’re welcome

[@Hello-bn2yc]

Brilliant ... Thank you... Sorry If its a repeatitive comment... Need to do it for the Alogrithm purpose!

[@AccessAnalytic]

Hah.. greatly appreciated

[@baskis69]

Thanks for sharing... It's really a game changer....I love it¡¡

[@AccessAnalytic]

You’re welcome

[@Simon-vc1wk]

Is it not a risk using preview features in live reporting for an Organisation? Just incase there is a bug from MS or they pull the feature. Preview features take considerable time to hit the live version of pbi but there is no way for admin to switch them off from a user perspective hitting the tenant. Just a thought

[@AccessAnalytic]

It’s a good point to raise. And I’d agree in this case. Although some features are less likely to be pulled than others

[@coollehavre]

thanks for this post its great but....this measures exists only in visuals like implicit measures...that is à bad practice.... what do you think about that ?

[@AccessAnalytic]

I think it addresses a real world problem that the “perfect” is simply too hard in many scenarios. People are already writing horribly complex dax that they don’t understand. This reduces the risk in those situations. It has limited application and should simplify edge cases. You still need model level DAX

[@chrism9037]

This is epic, thanks Wyn

[@AccessAnalytic]

No worries

[@arduiscane]

I recognize you cannot reference columns outside lf the visualization, but could you still use DAX that requires columns but instead just use *Rows"? For example, Im trying to build a calc that finds the earliest example of a value in a range of 90-100 without indicating anything beyond it. It looks like im going to need to use something more complicated than a switch or conditon tree. Those functions are available in the Calculated Column builder, but wont seem to work without the table references. Is there a way around this i.e. that Rows option? Thanks in advance!

[@AccessAnalytic]

I’m not quite following and it likely needs screenshots. I’d recommend posting to community.powerbi.com/t5/Desktop/bd-p/power-bi-designer Or www.reddit.com/r/PowerBI/

[@Drkalaamarab]

all that functions are easy in ms Excel but in PowerBI is little complex to calculate

[@AccessAnalytic]

Agreed

[@NeshaStanic]

Still not sure what checkbox should be checked in Preview features?

[@AccessAnalytic]

It might not be out yet in your version. It came out in the manual download version. Might not be in the auto-updating store version yet.

[@NeshaStanic]

@AccessAnalytic it was updated yesterday. Somebody on your LinkedIn post have answered, it's on the bottom of the list, and now all working fine. Thanks

[@AccessAnalytic]

@NeshaStanic ah yeah that was Jay ( he’s the Microsoft product manager in-charge of this feature 😃 )

[@yalqassab1]

Awesome 🎉

[@ajaaskelainen]

awesome!

[@joaorataoo]

It certainly makes DAX simpler, but does it make development better? We mustn't forget that one of the crucial and wonderful aspects of DAX is that the measures aren't tied to a specific context.

[@AccessAnalytic]

Agreed but in many cases the DAX is simply too hard for users to resolve. Or it gets done in a highly risky and overly complex way. This will have its own issues but will help a lot of people with simple scenarios

[@shubhabratadey]

Awesome tricks

[@อาทิตย์ประทุมถิ่น]

Thank for cumulative graph

[@AccessAnalytic]

You’re welcome.

[@NandagopalB]

I want to refer to the slicer at my table for some multiplication, is this possible?

[@AccessAnalytic]

I’m not sure what you mean sorry. Can provide an example and more detail

[@NandagopalB]

@@AccessAnalytic i have a sale table, having sales amount by region. And I have a slicer with percentages from 0-100. Based on the selection in the percentage slicer the value of sales amount should change. I can do this using measures. I want to achieve this in the new visual calculation function.

[@AccessAnalytic]

@NandagopalB you would still have to use a measure for this, or theoretically you could write the dax in the visual rather than a separate measure but not much point really.

[@Bhomiya22]

👍.. Visual calcs supported for line & bar , card visuals as well ? Any performance gap b/w visuals calcs or dax?

[@AccessAnalytic]

Yes it works the same for every type of visual. Calcs likely quicker on visual. I’m sure we’ll discover lots of quirks and limitations in coming weeks.

[@Bhomiya22]

@@AccessAnalytic , cool :)

[@shambhunathbm28]

Can i use Calculate function there.

[@AccessAnalytic]

Yes, but you can only reference values that are already in the visual

[@careless_breeze]

This is sort of things which we are using in tableau since ages .

[@AccessAnalytic]

Yes there’s a lot of work being done on the visualisations side to fill gaps against competitor offerings

[@eleonorebodinaud8494]

Hello Thank you so much for your video. I still don't have it on my Power BI... I have tried update it through the store, I also downloaded the latest version (2.126.1261.0), I still don't have the option.. Any help?

[@AccessAnalytic]

You also need to turn it on via options and settings > preview features

[@trunguc4911]

Are we able to combine CALCULATE with RUNNINGSUM? For example, could I calculate the cumulative usage kwh but from April (not from January in this matrix) with visual calculation?

[@AccessAnalytic]

You can write DAX to refer to any value that “is in the visual”, so you can’t write DAX to go and get other data from the model So if you added month number into the visual you could USE calculate to only do running sum if Month # > 3

[@nikolagrkovic8769]

Does percent of parent total works with field parameter?

[@AccessAnalytic]

I don’t think it’s currently compatible with field parameters.

[@nikolagrkovic8769]

You are right, tried it, it's not. Before that is possible, I can't consider it a game changer@@AccessAnalytic

[@dutchdatadude]

Not yet.

[@141balakrishna]

i dont see Hide Columns option in my report ? am ai missing something. (6:02)

[@AccessAnalytic]

You must be in edit calculation mode ( that caught me out too )

[@mirrrvelll5164]

Great one! but which update? feb 24? I still didn't get it (Germany)..

[@AccessAnalytic]

It’s in the manual download version ( might not be out in the self updating Store version )

[@Milhouse77BS]

Whaat! Did Feb Desktop come out on Friday?

[@AccessAnalytic]

No blog post yet but the manual desktop version was updated several hours ago.

[@Milhouse77BS]

@@AccessAnalytic downloading now Thanks!

[@danishnawaz3651]

Plz make Video for below Formula "Power Query", Video Time Fram is Max 10mint 1. List.RemoveMatchingItems 2. List.ReplaceMatchingItems 3. List.TransformMany 4. List.ReplaceValue 5. List.PositionOfAny 6. List.PositionOf 7. List.NonNullCount 8. List.Intersect 9. List.Durations 10. List.Difference 11. List.FindText 12. List.InsertRange 13. List.Union

[@AccessAnalytic]

I'd recommend you check out powerquery.how/ and see if Rick has linked videos there.

[@angelclarke1942]

I am not seeing this feature

[@AccessAnalytic]

It’s in the manual download version of desktop. Should be in the auto-updating store version in the next day or so

[@denwo1982]

With tools like Chatgpt, I would have ask it to do a similar Dax function

[@AccessAnalytic]

Fair enough but this really simplifies things. Still going to need more complex DAX for a lot of requirements / scenarios

[@hamidsajjadi6783]

Thank you for sharing this awesome feature. But you know? This feature reminds me of quick measure calculation.I think by DAX ,you will have more control over the filter context and consequently,by far it is deeper and more professional.

[@AccessAnalytic]

This is definitely aimed at making things easier for more users. Professional can be simple, just needs to be used in the right circumstances with the known intention.

[@andysuzhou7956]

Holy cow! Alberto just lost his job 😅

[@AccessAnalytic]

Hah… lots of limitations so Alberto is safe 😆

[@hamidsajjadi6783]

😂😂😂Marco also

[@MD-cu6wq]

😂

[@odeddror8916]

Looks like duplicating quick measures feature.

[@dutchdatadude]

It's nothing like that really.

[@AccessAnalytic]

Quick measures are pre-built DAX formulas. Visual calculations are are new concept where you can perform calculations on the numbers that exist in the visual and reference the rows as n d columns of data within that visual rather than the data model

[@hoges510]

It almost feels like cheating 😂

[@AccessAnalytic]

😀

[@hoges510]

@@AccessAnalytic I was hoping the visual level calcs would use the visually formatted number of decimals when it calculates, but it looks lke it still work off the underlying decimals i.e if set to currency, it will stil sum based off 4 decimals, even when the visuals are formatted to 2 decimals. I periodically get a 0.01 error in some of my currecy calcs (which are correct when using all 4 decimals), and assumed this would fix as its based on only seeing 2 decimals places, but it doesnt seem to be quite that way. Maybe i need to play around with it some more.

[@AccessAnalytic]

@hoges510 formatting is on the roadmap to address - not sure on timeline