SWITCH between PERCENTAGE and ABSOLUTE VALUES in one visual I Calculation Groups in Power BI
Ғылым және технология
In this video I show you how to create buttons to switch between % and absolute values.
Learn more about calculation groups in Power Bi.
Download file here datatraining.io/powerbi-how-to
--------------------------------
📊 TRAININGS 📊
---------------------------------
Power BI Design 4 Weeks Transformation Program my.datatraining.io/pages/powe...
Power BI Essentials datatraining.io/powerbilearni...
Business User Training datatraining.io/powerbi-busin...
For Custom Trainings and Consulting email directly support@datatraining.io
---------------------------------
⏱️ TIMESTAMPS ⏱️
---------------------------------
0:00 Intro
0:27 Getting Ready: Visual and Tabular Editor
1:43 Adding a calculation group with calculation items
4:20 Applying the calculation group
5:38 Formatting expressions in calculation groups
6:57 Show slicer with buttons
8:06 End
---------------------------------
😍 JOIN 😍
----------------------------------
Join bit.ly/4b453bi
Subscribe bit.ly/31MnQGO
Insta / howtopowerbi
LinkedIn / basdohmen
TikTok / how.to.power.bi
X / howtopowerbi
fb / howtopowerbi
Threads www.threads.net/@howtopowerbi
Newsletter datatraining.io/newsletter
---------------------------------
👇 CHECK THIS OUT! 👇
---------------------------------
💻 My gear amzn.to/47F21Yc
📚 Power BI books MUST READ! amzn.to/3tUfFcj
💡 General books I recommend amzn.to/48YNo33
🎶 Music for my videos www.epidemicsound.com/referra...
🚀 For growing on KZread: www.tubebuddy.com/bas
🏄 Stuff I use daily amzn.to/3HqfMQ2
* Above are affiliate links, which means at no additional cost to you, if you make a purchase using these links we will receive a small commission. It supports us and helps us to continue making more How to Power BI videos!
Thanks for being a part of this channel and all your support! 💪 🙏
#HowToPowerBI #PowerBI #DataTraining
#powerbidesktop #powerbitraining #powerbideveloper #DAX
Пікірлер: 106
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
3 жыл бұрын
thank you Simon for the nice words 😀 Indeed, the editing costs way more time than the recording haha
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
3 жыл бұрын
thank you for watching!!! 😀
Smooth. Need this early next week. Perfect timing Bas. Thx.
@HowtoPowerBI
3 жыл бұрын
haha I could see it in my crystal ball 😁😉
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!
Helemaal geweldig weer 😁. Very well explained, easy and fun to follow. And indeed also a nice introducer to Tabular Editor for me! Thanks.
@HowtoPowerBI
2 жыл бұрын
Dankjewel Ricardo!!! 😀😊 .. oh nice, worth exploring further!!!
Amazing video! Very helpful!
Very cool, Bas! Thank you!
Amazing video man! Super simple and super useful!
@HowtoPowerBI
Жыл бұрын
Thanks Ivan :)
Great tip ! Thank you !
Very good video, great result and effective. Thanks for sharing
@HowtoPowerBI
3 жыл бұрын
Glad to hear you find this helpful! Thanks for watching 👍
Great video!
Thanks, Bas, for abother great video. Very practical and useful.
@HowtoPowerBI
2 жыл бұрын
thx Artem!!!! 😎😀
Very nice. Great explanation. Keep going!
@HowtoPowerBI
3 жыл бұрын
thank you! more to come 😉
You are doing a great Job Buddy Keep going strong..
@HowtoPowerBI
3 жыл бұрын
thx Azar! 👊
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?
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.
Awsome video as usual!
@HowtoPowerBI
2 жыл бұрын
thx Tarik 😎👊
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 kzread.info/dash/bejne/X6Klp9hribuyo7g.html
Any ideas on how to deal with formatting issues regarding total labels on a stacked column chart or values within a gauge chart? Thanks!
Very elegant!
@HowtoPowerBI
3 жыл бұрын
Thank you Glenda! 😊
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.
Brilliant!
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
Super useful !!!
@HowtoPowerBI
3 жыл бұрын
thx Peter! 🙂
Perfect 👍
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
3 жыл бұрын
thanks Pedro... I think that's not possible yet.. would be nice though
Wow! Amazing
@HowtoPowerBI
3 жыл бұрын
Thank you Angelo! 😊
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?
You saved my life 😀 Thanks
@HowtoPowerBI
Жыл бұрын
Happy to help
Hi just wondering is it still possible to apply conditional formatting to the measures within the calculation group? Ie. the absolute and % totals
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
Thanks a lot!
@HowtoPowerBI
2 жыл бұрын
Thanks again for watching!
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!
Very niceVideo 👌
Great video, can you please help me how to create a switch from numbers to percentage without using tabula..
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
3 жыл бұрын
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
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!
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)
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
2 жыл бұрын
thank you João! it doesn't work? what version is your PBIRS?
Ultra practical and real-life application. How do you know I need this!?
@HowtoPowerBI
2 жыл бұрын
Haha thx!! Needed it myself one day 😉
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
2 жыл бұрын
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).
Is this method faster than just bookmarking two different graphs with their own measures? (e.g., hide/unhide graph base on user selection)
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
3 жыл бұрын
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
Great video Bas! Thanks for sharing this technique which I've wanted to add to my Power BI reports.
@HowtoPowerBI
3 жыл бұрын
thx Scott! happy to hear you can use it for some of your reports 😀
Please make some vids of tabular editor. Tq
@HowtoPowerBI
3 жыл бұрын
will do!
Bas, which book do you follow to learn Power Bi? Thanks for the video.
@HowtoPowerBI
2 жыл бұрын
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
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
6 ай бұрын
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
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
3 жыл бұрын
Hello, have you tried using Calculation Group?
@anastasiakailani3414
3 жыл бұрын
@@DanielTavares29 no actualy i do not know how.. because i cannot use the selected measure because it is a different calculation
Hi Bas, can u format the measures in the Power BI instead of the Tabular editor, right?. Thanks in Advance.
@HowtoPowerBI
3 жыл бұрын
Hi Juan, you can set the format for measures in Power BI. However, not with a format expression like shown in the video.
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?
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
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
3 жыл бұрын
could it be that you turned off the interaction from the slicer to the stacked bar chart ?
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
3 жыл бұрын
You could use the technique from the video in combination with bookmarks 🙂
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
3 жыл бұрын
The link for download - The page wasn't found
@HowtoPowerBI
3 жыл бұрын
thx for pointing out! fixed it should work now
@HowtoPowerBI
3 жыл бұрын
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.
Can i do this without the tublar editor? It sounds complicated with this third party source
I am stuck in between I dont see the tables in the tabular can you help. Thanks in advance
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
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
3 жыл бұрын
could it be that you turned off the interaction from the slicer to the clustered bar chart ?
@shailasarker4193
3 жыл бұрын
@@HowtoPowerBI No, I've checked that as well
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.
Unable to set the property Discource Implicit measure to FALSE. Any ideas, pls?
Is there a way to do this using Dax Studio?
@HowtoPowerBI
3 жыл бұрын
no, I believe not
@trishnasingh3952
3 жыл бұрын
Thank you for the reply. Your content is amazing.
my calculation group is not filtering the data any idea why?
A great tutorial. But you mentioned there was another way to do this. But you did not show what it is.
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
3 жыл бұрын
what did you type in for your expressions /format string expressions?
It is calculating % of Total as % of all years combined. Every year/period should be 100%. Please help
Brilliant