SWITCH between PERCENTAGE and ABSOLUTE VALUES in one visual I Calculation Groups in Power BI

[@simonloughnane6683]

What a great practical example of using calculation groups, and also one that is not too intimidating for people (like me) who are new to them. I must commend you on the editing and production of your videos, they are so slick. No doubt a lot of work goes into achieving that, thank you.

[@HowtoPowerBI]

thank you Simon for the nice words 😀 Indeed, the editing costs way more time than the recording haha

[@xodrinker]

Smooth. Need this early next week. Perfect timing Bas. Thx.

[@HowtoPowerBI]

haha I could see it in my crystal ball 😁😉

[@timestoryx]

Keep going 🎉🎉🎉

[@BIGorilla]

Great video ! The part on the format expression is very effective to get a very flexible report. The calculation groups also are a real solution to keep the performance snappy. Looking forward to your next video!

[@HowtoPowerBI]

thank you for watching!!! 😀

[@azarmeeza5102]

You are doing a great Job Buddy Keep going strong..

[@HowtoPowerBI]

thx Azar! 👊

[@ricardopinxt212]

Helemaal geweldig weer 😁. Very well explained, easy and fun to follow. And indeed also a nice introducer to Tabular Editor for me! Thanks.

[@HowtoPowerBI]

Dankjewel Ricardo!!! 😀😊 .. oh nice, worth exploring further!!!

[@isidrebague3659]

Very good video, great result and effective. Thanks for sharing

[@HowtoPowerBI]

Glad to hear you find this helpful! Thanks for watching 👍

[@DilbagSingh-vf6gv]

hello ,im new into data analyst. firstly your videos are wonderful, to the points and explains everything. every now and then i goes to your channel to clear my doubts or learn something new. secondly you mentioned two approachs to percentage problem and expalined only on, can you put light on another way too, as tabular editor is paid software and i cannot right now buy it.

[@ikenigs]

Amazing video man! Super simple and super useful!

[@HowtoPowerBI]

Thanks Ivan :)

[@KaterynaYakubovska]

Very helpful! Thanks!

[@juliazjochova4737]

Very cool, Bas! Thank you!

[@GeraBizuneh]

Great tip ! Thank you !

[@pedropradocarvalho]

Very good video, I wonder if we can adjust other format settings, like from horizontal to vertical orientation on the datalabels for the visual, for instance.

[@HowtoPowerBI]

thanks Pedro... I think that's not possible yet.. would be nice though

[@guilhermefloragarcia1846]

Amazing video! Very helpful!

[@artemburov9456]

Thanks, Bas, for abother great video. Very practical and useful.

[@HowtoPowerBI]

thx Artem!!!! 😎😀

[@EricaDyson]

Very nice. Great explanation. Keep going!

[@HowtoPowerBI]

thank you! more to come 😉

[@panpan3246]

Thanks so much! One question wants to seek your help, when I switch to "% of Total", why it shows the percentage of the total number instead of percentage of current month?

[@tonypcoyle]

are you getting the same %ages across all dates? if so, check your REMOVE FILTER: it should only remove the filter for the CHANNEL dimension... OR You don't have a relationship between your dates and your facts (i.e. you are using the DATE from your CHANNEL table - when you remove the filters, it also removes the date filter in that table).

[@gennadypronin8306]

I like your content submission! That's cool! A year ago I tried to solve the problem with displaying percentage and number in a radio button, but I still couldn't find how to do it. Thanks for the solution!

[@bukalter]

You saved my life 😀 Thanks

[@HowtoPowerBI]

Happy to help

[@pl4195]

in the remove filter part, if I have a field parameter instead of "single selection channel", how can I adjust the formula? thank you.

[@Tp01738]

I’m looking for a solution here too. Using field parameters to switch between measures in a visual, and would like to add a slicer with a calculation group to change between absolute / % breakdown

[@lucianozx]

Hi, Thanks for the teachings, in a didactic and simple way, you are the greatest. Can you help me? I have two Bar Charts and one Card, and all working ok, as your example (% or absolute in all of them), can you please tell me how to exclude Card, so that it always shows the absolute value only, and never switch to %?

[@HowtoPowerBI]

Thanks Luciano! select the slicer, go to format on the top ribbon, then edit interactions, and turn off the interaction to your card. 🙂 that should do it

[@MoinKhan-1707]

Wow its amazing bass. but I have a question My Organization not allowed tabluar editor. how can I create this measure in power BI? I think now there should be updates in power BI itself because this video is 3 years old.

[@joaorataoo]

Very clean and handy. I'm working with the Power BI reports server. Do you know if there is any way to work with the tabular in Power BI RS?

[@HowtoPowerBI]

thank you João! it doesn't work? what version is your PBIRS?

[@computarik]

Awsome video as usual!

[@HowtoPowerBI]

thx Tarik 😎👊

[@fraggle200]

Great vid that shows exactly what I was wanting to do... only downside is that after I bring in the calculation group any field I add to a table will only show distinct values and nothing else. it completely breaks the pbix. Can't find how to resolve it either.

[@joelgonzales720]

Is there any workaround for being able to include calculations in these? For example, if I wanted to calculate variance and be able to switch between absolute and %?

[@HowtoPowerBI]

Yes, in the example I use sales, but you could put in variance of sales or any other measure and it would work as well

[@bentatlim]

Very niceVideo 👌

[@supreethalahari.sreeram5147]

Great video, can you please help me how to create a switch from numbers to percentage without using tabula..

[@a.7388]

Is this method faster than just bookmarking two different graphs with their own measures? (e.g., hide/unhide graph base on user selection)

[@DanialWard]

Hi just wondering is it still possible to apply conditional formatting to the measures within the calculation group? Ie. the absolute and % totals

[@DanielTavares29]

I am not sure if external tools are available for Power BI Report Server, if they don't would it be possible to create something similar using bookmarks?

[@DanielTavares29]

The link for download - The page wasn't found

[@HowtoPowerBI]

thx for pointing out! fixed it should work now

[@HowtoPowerBI]

I think it should work. If you would like to use bookmarks, You could create two overlapping visuals and then show the pct chart with one bookmark and one bookmark that shows a chart with absolute values. Then create two buttons and add bookmark actions that will allow you to toggle between the two charts.

[@igreen4460]

Brilliant!

[@carax006]

Wow! Amazing

[@HowtoPowerBI]

Thank you Angelo! 😊

[@sonetmollick4630]

Thanks a lot!

[@HowtoPowerBI]

Thanks again for watching!

[@petergolovatyi2447]

Super useful !!!

[@HowtoPowerBI]

thx Peter! 🙂

[@djbastoni_au]

Thanks for this. Well explained and I think I can implement after downloading tabular editor (been watning to unleash its power for a while now).

[@HowtoPowerBI]

You can do this now in an easier way with field parameters kzbin.info/www/bejne/Y5_Udqlojr1_qbc

[@MrTerenceJonathan]

Hi this is really good stuff I need a help on my data were I have the year from 2021~23 along with the dates & months in a column in such a scenario how can I measure the YoY & MoM. Please guide. Thanks Terence

[@kbelinho08]

Hi Is there any way to show a graph like clustered chart 100%, but instead of to show percent value show absolute values? the creative by customer is insane. I'll appreciatte your help. thanks

[@ИнИнг-ъ7в]

Perfect 👍

[@klgyal2010]

Great video!

[@anastasiakailani3414]

Perfect Videooo once again.. I have a problem with a switch.... I make a switch measure with sales and quantity but when i choose sales i need to have the euro sign € and when i choose quantity i need to have just the number.. any ideas?

[@DanielTavares29]

Hello, have you tried using Calculation Group?

[@anastasiakailani3414]

@@DanielTavares29 no actualy i do not know how.. because i cannot use the selected measure because it is a different calculation

[@shermansee]

Ultra practical and real-life application. How do you know I need this!?

[@HowtoPowerBI]

Haha thx!! Needed it myself one day 😉

[@sonetmollick4630]

Bas, which book do you follow to learn Power Bi? Thanks for the video.

[@HowtoPowerBI]

thx Sonet! most stuff I learn from projects I work on. There are many information resources on the internet that provide different angles on each topic - I prefer that over any book

[@amirhd2080]

Please make some vids of tabular editor. Tq

[@HowtoPowerBI]

will do!

[@malvindersondh3563]

Great and easy to follow tutorial!! I have one question, I have created a drill down page that takes in the selected data points from the chart and that creates the label on the top Of the drill down page. This works fine when I use the value but when I drill from % toggle it gives an error along the lines of trying to convert the label text into a numerical. Any ideas on how I can get this working for both value and per tags toggles?

[@AbouAli01006300091]

Brilliant

[@juanignacioXI]

Hi Bas, can u format the measures in the Power BI instead of the Tabular editor, right?. Thanks in Advance.

[@HowtoPowerBI]

Hi Juan, you can set the format for measures in Power BI. However, not with a format expression like shown in the video.

[@mekis888]

Hi, just a side question. At 0:45 there is a dimCalendar table. When you add Year and Month from that on the visualization month appears as Jan, Fab, and so on. How could it be changed to show not the name of the month, but the number? (01 instead of Jan, 02 instead of Feb)

[@Dritty]

Thanks Bas. Really clear explanation. I have a question/problem after I implemented this solution. I cannot add/dragg a (numeric) column to the 'Values' of a visual. I can only add/dragg a measure. So I have to make a measure of every column I want to visualize. Is there a way to fix this?

[@alexanderricht8855]

Any ideas on how to deal with formatting issues regarding total labels on a stacked column chart or values within a gauge chart? Thanks!

[@trishnasingh3952]

Is there a way to do this using Dax Studio?

[@HowtoPowerBI]

no, I believe not

[@trishnasingh3952]

Thank you for the reply. Your content is amazing.

[@lastdance903]

Hi Bas, great video! I have a very similar setting as yours, except that instead of showing the data label for each data point I only show the total label. After I did the calculation group formatting string, It seems that the formatting does not apply to the total label despite the fact that the unit of y-axis changes in accordance with formatting setting with no issues. Can you please check if this is the case with yours as well? Thanks

[@dharanidhar4875]

I have four buttons like table and visual and separate one as percentage and value,if I select table button and value button it has to display in the format of tables and vice versa for visual top,can you suggest me on that

[@HowtoPowerBI]

You could use the technique from the video in combination with bookmarks 🙂

[@AnkitGupta-cn1zd]

I am stuck in between I dont see the tables in the tabular can you help. Thanks in advance

[@hamed12345100]

Can i do this without the tublar editor? It sounds complicated with this third party source

Thank you very much Bas for sharing great tips! I have a question, maybe you can help me... how can I get the name of the calculated item to create a dynamic title as selected? I have tried creating virtual relationship with disconnected table, applying SELECTEDVALUE, SELECTEDMEASURE, SELECTEDMEASURENAME, but no good results. Greetings from Peru!

[@marcvandewall804]

Tabular Editor is a great tool but I'm currently running into a problem. When opening Tabular Editor 2.x or Tabular Editor 3 in a SSAS model (pushed from a TimeXtender ETL environment) it says: "No database with ID or Name 'Sales' (cube is called sales) was found on the server, or the user account does not have administrative rights. Did I miss something in the setup to activate this with SSAS cubes?

[@amirbitan]

Hi Bas, Great video. Something is not working in my report: I have two visuals in the report, a card and a stacked bar chart. in the card the percentage works fine, but in the chart the % is not visible Thanks, Amir

[@HowtoPowerBI]

could it be that you turned off the interaction from the slicer to the stacked bar chart ?

[@seifeldineelgowakhy3734]

I have tried it but unfortunately nothing changes on the chart, as if the slicer has no effect on the original table that has the data

[@rahulhatle5036]

my calculation group is not filtering the data any idea why?

[@ricardopinxt212]

Made already good use of this 😁. Although I now encountered a situation where I had set my values to show as thousands and the percentage view did not like that.... When i switch back to Auto the percentages showed just fine but my absolutes are in Millions and thus show 0M or 1M....🤨 Have you encountered this before and is there a solution for this behaviour? *EDIT*: Well found out today about the new feature 'Field Parameters' so no need for complicated Tabular Editor anymore 😁. This new feature works like a charm!

[@shailasarker4193]

Hi there, I didn't use any legend in my Clustered Bar chart and my slicer of Absolute/% of Total is not working on the clustered bar chart..it's only effective on my Multi Row Card..what can be done regarding this..please help

[@HowtoPowerBI]

could it be that you turned off the interaction from the slicer to the clustered bar chart ?

[@shailasarker4193]

@@HowtoPowerBI No, I've checked that as well

[@vishalramanathan110]

Hi, When I used a calculation group and powered a line chart it was working fine. But due to some reason the other elements in my report are getting affected. If I put in any measure which is not a part of the group in a KPI card it seems to show the same value though it shouldn't. Can someone help me fix this?

[@zL0ki]

I think you are experiencing the same issue I had. You need to adjust the format string expression to stop it acting on other visuals. As the clip just shows one visual this doesn't crop up in the demo. Add something like this to the format string expression of the '% of total' e.g. VAR SkipConversion = SWITCH( TRUE(), SELECTEDMEASURENAME () = "Measure 1", 1, SELECTEDMEASURENAME () = "Measure 2", 1, 0 ) VAR percAmount = ("0%") VAR absAmount = ("#") RETURN IF (SkipConversion, percAmount, absAmount) So any measure you want has the correct percentage conversion. The 2nd measure was there for me as I had another visual that used percentages from another measure. Without it, using the above would force it not to be a percentage. I think this is the one disadvantage with using this method. It can easily break other parts of your report and introducing technical debt.

[@SergeiDumnov]

Unable to set the property Discource Implicit measure to FALSE. Any ideas, pls?

[@scottwilliams6741]

Great video Bas! Thanks for sharing this technique which I've wanted to add to my Power BI reports.

[@HowtoPowerBI]

thx Scott! happy to hear you can use it for some of your reports 😀

[@vanessamondillon6484]

Hello, I try to make a Switch in my data model but if I change the format string expression in the Switch all my table change ... Someone can help me ?

[@HowtoPowerBI]

what did you type in for your expressions /format string expressions?

[@lijunchen]

A great tutorial. But you mentioned there was another way to do this. But you did not show what it is.

[@sadafjamal8307]

It is calculating % of Total as % of all years combined. Every year/period should be 100%. Please help