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.
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.
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.
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!
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
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.
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
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.
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?
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).
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 %?
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
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?
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!
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?
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!
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 %?
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
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?
Can i do this without the tublar editor? It sounds complicated with this third party source
3 года назад
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!
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?
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.
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
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
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)
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?
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
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?
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.
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
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 ?
![Coheed and Cambria - Searching for Tomorrow [Official Video]](http://i.ytimg.com/vi/hDsYGLbmadg/mqdefault.jpg)
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.
thank you Simon for the nice words 😀 Indeed, the editing costs way more time than the recording haha
Smooth. Need this early next week. Perfect timing Bas. Thx.
haha I could see it in my crystal ball 😁😉
Keep going 🎉🎉🎉
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.
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.
Great video, can you please help me how to create a switch from numbers to percentage without using tabula..
You are doing a great Job Buddy Keep going strong..
thx Azar! 👊
Very good video, great result and effective. Thanks for sharing
Glad to hear you find this helpful! Thanks for watching 👍
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!
thank you for watching!!! 😀
in the remove filter part, if I have a field parameter instead of "single selection channel", how can I adjust the formula? thank you.
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
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.
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
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.
thanks Pedro... I think that's not possible yet.. would be nice though
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?
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).
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 %?
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
Is this method faster than just bookmarking two different graphs with their own measures? (e.g., hide/unhide graph base on user selection)
Thanks, Bas, for abother great video. Very practical and useful.
thx Artem!!!! 😎😀
Amazing video man! Super simple and super useful!
Thanks Ivan :)
Very helpful! Thanks!
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?
Very cool, Bas! Thank you!
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!
You saved my life 😀 Thanks
Happy to help
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?
Hello, have you tried using Calculation Group?
@@DanielTavares29 no actualy i do not know how.. because i cannot use the selected measure because it is a different calculation
Helemaal geweldig weer 😁.
Very well explained, easy and fun to follow.
And indeed also a nice introducer to Tabular Editor for me!
Thanks.
Dankjewel Ricardo!!! 😀😊 .. oh nice, worth exploring further!!!
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?
thank you João! it doesn't work? what version is your PBIRS?
Amazing video! Very helpful!
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).
You can do this now in an easier way with field parameters ruclips.net/video/-nqEv2YXLsU/видео.html
Great tip ! Thank you !
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!
Very nice. Great explanation. Keep going!
thank you! more to come 😉
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 %?
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
Brilliant!
Any ideas on how to deal with formatting issues regarding total labels on a stacked column chart or values within a gauge chart? Thanks!
Hi just wondering is it still possible to apply conditional formatting to the measures within the calculation group? Ie. the absolute and % totals
Brilliant
Bas, which book do you follow to learn Power Bi? Thanks for the video.
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
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?
Ultra practical and real-life application. How do you know I need this!?
Haha thx!! Needed it myself one day 😉
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!
Very niceVideo 👌
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?
The link for download - The page
wasn't found
thx for pointing out! fixed it should work now
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.
Great video Bas! Thanks for sharing this technique which I've wanted to add to my Power BI reports.
thx Scott! happy to hear you can use it for some of your reports 😀
Wow! Amazing
Thank you Angelo! 😊
Hi Bas, can u format the measures in the Power BI instead of the Tabular editor, right?. Thanks in Advance.
Hi Juan, you can set the format for measures in Power BI. However, not with a format expression like shown in the video.
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
could it be that you turned off the interaction from the slicer to the stacked bar chart ?
Awsome video as usual!
thx Tarik 😎👊
Please make some vids of tabular editor. Tq
will do!
Perfect 👍
Thanks a lot!
Thanks again for watching!
Super useful !!!
thx Peter! 🙂
I am stuck in between I dont see the tables in the tabular can you help. Thanks in advance
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
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)
Great video!
Unable to set the property Discource Implicit measure to FALSE. Any ideas, pls?
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?
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
You could use the technique from the video in combination with bookmarks 🙂
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
my calculation group is not filtering the data
any idea why?
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?
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.
Is there a way to do this using Dax Studio?
no, I believe not
Thank you for the reply. Your content is amazing.
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
could it be that you turned off the interaction from the slicer to the clustered bar chart ?
@@HowtoPowerBI No, I've checked that as well
A great tutorial. But you mentioned there was another way to do this. But you did not show what it is.
It is calculating % of Total as % of all years combined. Every year/period should be 100%. Please help
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 ?
what did you type in for your expressions /format string expressions?