Thank You for this tutorial Nestor :-) I've solved my problem!
@NestorAdrianzen
3 жыл бұрын
Glad you found it helpful! Keep up the good work, Marek 👊
@robertsolis6129
2 жыл бұрын
Best video I hae seen calculating % based on Column, I watched 6 before finding this one. This is to follow and understanable
@NestorAdrianzen
2 жыл бұрын
Glad you found the tutorial helpful, Robert. Cheers 👍
@NestorAdrianzen
4 жыл бұрын
What's up, guys! Which method did you find more helpful? Let me know by leaving a comment below. Cheers! a) "Calculated measure" b) "Show value as percent of grand total"
@saikumarchebrolu1071
4 жыл бұрын
Plz do more videos on power bi realtime scenarios
@NestorAdrianzen
4 жыл бұрын
Will do! Keep up the good work, Saikumar.
@aperxmim
Жыл бұрын
Easy Explanation! Easy to Learn!
@NestorAdrianzen
Жыл бұрын
Glad to hear that. Cheers!
@christyhart1777
2 жыл бұрын
my Percentage was calculating more then 100 for the column total, I have been trying to figure this out for days, watched multiple videos, You explained this perfectly and it now it works, thank you
@NestorAdrianzen
2 жыл бұрын
Awesome! Glad it was helpful 👍
@mohammedabdulwahab4152
8 ай бұрын
Awesomes video , I Created Income statements, the First row item is total sold quantity(sales volume) and then the Revenue(3 sub Product), Cost of Sale(5 sub line), Gp, Direct cost element(10 sub line), operating expenses(sub line 10) then Net profit, I need to divided all Main and subline item with sales volume to figure out the Rate per unit for each row, This is model used in Readymix Industry, Appreciate your support in writing measure for my model
@NestorAdrianzen
8 ай бұрын
Glad to hear that. Cheers!
@rakeshmahakud110
2 жыл бұрын
Finally got solution after searching whole day, thanks for your efforts
@NestorAdrianzen
2 жыл бұрын
Great! Keep up the good work, Rakesh!
@anantshayanasanjeev9025
Жыл бұрын
Thank you for tutorial. I tried with the same 2 calculations using India's population data. One thing I observed it % and total % differs when we choose few items. 1. With Variable calculation. it wont show 100% in Total % but with auto "Show as % of Total" it always calculated based on filter selection.
@NestorAdrianzen
Жыл бұрын
Anantshayana Sanjeev , Thanks for stopping by. Instead of ALL, try using ALLSELECTED. If you add any filter, the result will still give 100% just for the items that you are selecting. Cheers!
@mansijswarnkar4389
2 жыл бұрын
Thanks for showcasing % in MS PowerBI using measures; it is simply incredible, and I believe it is dynamic as well which works will the different options as well. thanks again
@NestorAdrianzen
2 жыл бұрын
Thanks for your support, Mansij!
@loganmcallister6874
3 жыл бұрын
Thank you!! Your tutorial on the variables was exactly what I needed, and solved a problem I have been facing for hours!
@NestorAdrianzen
3 жыл бұрын
Excellent! Glad it help. Keep up the good work, Logan.
@daveybalmer
3 жыл бұрын
Hi Nestor - you saved me a lot of time here. I never noticed the option in the Fields pane to "show value as a percentage of the grand total" before. Great video.
@NestorAdrianzen
3 жыл бұрын
Charles, Glad it helped! Cheers!
@gospelmoto2833
Жыл бұрын
Great video sir. It helped me. Well explained. You must be a teacher by profession. I like the way/method you use - you explain the theory and backed it up with practical examples. Thanks for sharing your knowledge.
@NestorAdrianzen
Жыл бұрын
Glad it was helpful!
@Alhalmeya
2 жыл бұрын
nester words do not do justice when i say - thank you so much i love you
@NestorAdrianzen
2 жыл бұрын
Abyss, Thanks for your support and keep it up!
@anuk9846
2 жыл бұрын
Thanks a lot bro this video means a lot to me
@NestorAdrianzen
2 жыл бұрын
Glad you found it helpful. Cheers 👍
@mqurram
2 жыл бұрын
Amazing teacher , Very beautiful explanation , Thank you very much sir
@NestorAdrianzen
2 жыл бұрын
Mohammed, glad you found the tutorial helpful. Keep it 🆙
@vinayn8333
2 жыл бұрын
Thank you so much.
@NestorAdrianzen
2 жыл бұрын
No problem!
@shubhabratadey
2 жыл бұрын
Simply Great
@NestorAdrianzen
2 жыл бұрын
Glad to hear that, Shubhabrata!
@ariarc
3 жыл бұрын
Boom! You rock brother. Thanks for the tutorial.
@NestorAdrianzen
3 жыл бұрын
You bet! Bom!💥
@CanadaTorontoLife
2 жыл бұрын
For the first method, I have a requirement to change percent of total on filter selection as well. What can I do? Like if we have 2 states selected percent of total should be based on that.
@NestorAdrianzen
Жыл бұрын
SAMBANDHAN, Thanks for stopping by. It seems that the ALLSELECTED function will give you what you are looking for. Just substitute the ALL function for ALLSELECTED. I hope it helps!
@farhodrahimov3365
2 жыл бұрын
Thank you 😊
@NestorAdrianzen
2 жыл бұрын
You're welcome 👍
@olgadurham9843
3 жыл бұрын
Thank you so much! It is right what I need!
@NestorAdrianzen
3 жыл бұрын
Glad it was helpful, Olga. Keep up the good work!
@margaretforde1721
2 жыл бұрын
Thank you
@NestorAdrianzen
Жыл бұрын
Thanks for stopping by, Margaret!
@longt0209
3 жыл бұрын
How do I do the first first option but still allowing page filters for date ...etc to drill through. Looking at specific period? Thanks
@NestorAdrianzen
3 жыл бұрын
Long T, Thanks for stopping by. If you want to filter dates, you might want to create a calendar table and connect it with the claims table. Then use a slicer referencing any column from the calendar table. I hope this helps!
@shaikjilani8802
Жыл бұрын
How to calculate % if the colomns are in diffternt tables. I mean i have a target table and Sales table i want to calculate the % using sales value and target value i.e.., Sales/Target can you pelase suggest
@NestorAdrianzen
Жыл бұрын
Have you tried combining the tables with Power Query? If it doesn't work, you might want to create a calculated column with the RELATED or RELATEDTABLE functions and then perform the ratio calculation when both sales and target fields are in the same table. This video might help: kzitem.info/news/bejne/uW5juniCqnSch6A
@andywang6918
2 жыл бұрын
AWESOME TUTORIAL MAN!!!
@NestorAdrianzen
2 жыл бұрын
Glad you liked it! Keep it up, Andy.
@jakedelarosa2790
2 жыл бұрын
Great video. By any chance, how do we get the average percentages of table shown. I would like to get it by getting the average percentages of the rows in the table.. Pleasse
@NestorAdrianzen
2 жыл бұрын
Jake, Thanks for stopping by. For averages at a row level, you might want to use the AVERAGEX function to as part of your measure. I hope this helps!
@jakedelarosa2790
2 жыл бұрын
@@NestorAdrianzen Thanks, i will try this.
@suchitraroutray4705
3 жыл бұрын
I have a query...in a matrix,when i add the feature subtotal and total on , the sum of individual rows data in % exceeding 100 5 but still it shows the grand total as 100%..is it works like that only??
@NestorAdrianzen
3 жыл бұрын
@Suchitra Routray. Thanks for stopping by. You might want to use a combination of CALCULATE and KEEPFILTERS. This tutorial might help. kzitem.info/news/bejne/1JVvuo2djYSki44
@JunaidHussain73
2 жыл бұрын
Hi, I have sheet in Power Bi with just 3 columns, Date, "Quotation Amount" and Status (win or not win), I need to calculate the percentage of quotations which has status as win against the sum of all "quotation amount". Right now my Power Bi shows only Sum of "Quotation amount" and sum of Quotation which has status as win
@NestorAdrianzen
2 жыл бұрын
Junaid, Thanks for stopping by. What you can do is to create a measure to find the percentages. You can use variables and the CALCULATE function to create that measure. Then use an slicer to filter status (select win). See below an example: Quotation % := VAR QuotationActual = SUM('Table'[Quotation Amount]) VAR QuotationTotal = CALCULATE( SUM('Table'[Quotation Amount]), ALL('Table'[Quotation Amount],'Table'[Date]) ) RETURN DIVIDE(QuotationActual,QuotationTotal)
@moatazmohamed3568
2 жыл бұрын
First, Thank You for this tutorial, but second way not working when we compare with 2 years
@vyasrahul
3 жыл бұрын
I have a data sheet where I have data, net sales, and expenses. I want to find my net profit based on the percent I select for forecasting. For example, in Jan I selected 5%Margin , Feb 8% Margin and so on and so forth. Is there a way?
@NestorAdrianzen
3 жыл бұрын
R V, Thanks for your question. It seems that this tutorial will give you some guidance. Take a look at 3rd question of the case. I hope it helps. Cheers! kzitem.info/news/bejne/moeK1qGPkKdmrI4
@mansijswarnkar2183
Жыл бұрын
Nice tutorial; btw how to show the % variation of total value WtW. Thank you
@NestorAdrianzen
Жыл бұрын
Thanks for stopping by. For period-over-period calculations, you might want to check the following tutorial: kzitem.info/news/bejne/lGitqp6qiXSphI4 I hope it helps.
@mansijswarnkar2183
Жыл бұрын
@@NestorAdrianzen Sure, Thank you so much replying !
@domnorth952
3 жыл бұрын
How would this work for percent of row total?
@NestorAdrianzen
3 жыл бұрын
Dom North, Thanks for stopping by. This tutorial might help. Cheers! kzitem.info/news/bejne/mn-arKVomoKpoI4
@analistagestordelainformac9583
3 жыл бұрын
It is necessary to use SUMX? I think that a clasic calculate(sum(all can do it right?
@NestorAdrianzen
3 жыл бұрын
You are correct! We can also use CALCULATE to solve percentages. This tutorial has an example with CALCULATE. Cheers! kzitem.info/news/bejne/mn-arKVomoKpoI4
@slnsrinivaskunapuli8093
4 жыл бұрын
Hai, i am working in security agency. My company is interested in using power bi for data analysis but in any case our machines cannot be connected internet. Can u please help to purchase offline activation power bi or suggest me any alternative solution
@NestorAdrianzen
4 жыл бұрын
K S L N Sinivas, Thanks for your question. The free application, Power BI Desktop, works perfectly fine without access to the internet, but it's limited to work with local excel files only. However, If your company wants to connect to different databases or publish reports to Power BI Service, internet access is required. For this last option, there are a couple of alternatives: Power BI Pro and Power BI Premium. If your company is small and the budget is not strong, I'd suggest to start getting Power BI Pro. Here is a link to see the differences. I hope it helps! powerbi.microsoft.com/en-us/pricing/#powerbi-comparison-table
@slnsrinivaskunapuli8093
4 жыл бұрын
@@NestorAdrianzen sir, thank you very much the prompt response. As per my company norms, sofware should have licensed version which is required to be used in our private network. How can i get powr bi pro ver offline or suggest me to overcome the situation
@NestorAdrianzen
4 жыл бұрын
@@slnsrinivaskunapuli8093, Unfortunately, an internet connection is needed for Power BI Pro to work properly.
@kennethstephani692
3 жыл бұрын
Excellent video!
@NestorAdrianzen
3 жыл бұрын
Glad it was helpful, Kenneth. Keep up the good work 👊
@rmccallen
3 жыл бұрын
I have brand data by year and want to calculate the percentage each brand represents for each year in a table that has five brands and four years. This DAX measure works great however it returns the percentage each brand represents for the aggregate of all four years of data. How do I change this to calculate each year independently?
@NestorAdrianzen
3 жыл бұрын
Roger, Thanks for stopping by! I would suggest to use the CALCULATE function to find percentages. Have you taken a look at this video? I hope it helps. kzitem.info/news/bejne/mn-arKVomoKpoI4
@sajidshaikh2727
3 жыл бұрын
Hi Sir, I have data in a column. The value in column are: Clean, Suggestion, Semi Critical, and Critical. Now I want to perform an arithmetic function as- Clean+Suggestion/Clean+Suggestion+Semi Critical+Critical Could you please guide me how to do it in Power BI?
@NestorAdrianzen
3 жыл бұрын
Sajid, Thanks for stopping by. It seems that you are trying to sum columns with the exception of this ratio (Suggestion/Clean). First, you might want to create a new column for the ratio (Column1: Suggestion/Clean). This will give you one value for each row. After that, you can create a measure like the following: Measure := SUM(Clean) + SUM (Column1) + SUM(Suggestion) + SUM (Semi Critical) + SUM (Critical) I hope this can point you in the right direction. Thanks!
@olufemisanchez9773
3 жыл бұрын
Thank you for this, please is there a way I can drag or bring the % Grand Total to the fields section?
@NestorAdrianzen
3 жыл бұрын
Olufemi Sanchez. Thanks for stopping by. You can achieve that by using measures. This tutorial will help! kzitem.info/news/bejne/mn-arKVomoKpoI4
@stxclos
2 жыл бұрын
seria bueno que también subieras los videos en español, para los que no somos dados a la lengua inglesa.
@NestorAdrianzen
2 жыл бұрын
Marlon, gracias por revisar el contenido. Acá puedes encontrar la versión en español. Saludos! kzitem.info/news/bejne/y6WLl2ycqXaKpmU
@ihsanahmad9364
3 жыл бұрын
Hi Nestor, Nice tutorial. I've tried with your PBIX file. when I add filter Slicer 'Claim Year' and select single year, percentage is different with other table (using show value as percent of grand). But if i select All 'Claim Year' in slicer the value is same alias correct. could you assist us how create percentage with filter (such as selected 'year', 'month', etc)
@NestorAdrianzen
3 жыл бұрын
Ihsan, Thanks for stopping by. For this particular case and for the total measure, you might want to use the CALCULATE function. This function modifies the filter context. Also, you might want create a Calendar table and connect it to the Claims table (Based on Claims[Claims Date]). Then use the date column form the Calendar table to slice and dice. The new total measure would look like the following (This will be the new denominator for the percentage measure): Total_Collected_Premium := CALCULATE( [Collected_Premium], FILTER( ALL(Claims), Claims[Claim Date] >= MIN(Claims[Claim Date]) ), FILTER( ALL(Claims), Claims[State] IN {"Arizona","California", "Georgia", "South Carolina", "Texas", "Tennessee"} ) ) I hope it helps. Welcome to DAX!
@ricardoftjunior
3 жыл бұрын
Hi Nestor, great video, thanks for sharing your knowledge! This solution works for a one-level percentage. What if I have more than one level of information and want the percentage based on each level? For example, I have the following table: Type | Model | Qty Car | modelA | 50 Car | modelB | 30 Car | modelC | 20 Bike | model1 | 25 Bike | model2 | 35 Bike | model3 | 40 And I want to present the percentage within each type in the same matrix: Car = 100% Car / modelA = 50% Car / modelB = 30% Car / modelC = 20% Bike = 100% Bike / model1 = 25% Bike / model2 = 35% Bike / model3 = 40%
@NestorAdrianzen
3 жыл бұрын
Ricardo, Thanks for stopping by. For that case, you might want to use the CALCULATE function to modify the filter context in your visuals. This video might help: kzitem.info/news/bejne/mn-arKVomoKpoI4. For the denominator, you can try the following: VAR var1 = CALCULATE( [Expression], ALLEXCEPT(Table, Table[Type]) )
@rtrkings1173
4 жыл бұрын
Very useful 👍 thank u ❤️
@NestorAdrianzen
4 жыл бұрын
Welcome 😊
@jrazafindrabe5229
3 жыл бұрын
Hi Nestor, What if One would like to Calculate Percentages Based on one value in a Category. For Example , suppose that in a category called "Country", there are 4 Countries, USA, France, United Kingdom and Germany. How would calculate the Percentages of Sales of the rest of the Countries Based on 100% USA.
@NestorAdrianzen
3 жыл бұрын
J Raz, Thanks for stopping by. Welcome to DAX. What you can do is create an additional measure just for USA. Then, use the DIVIDE function to find the ratio. Please see below for more details: ------------------------------ USA Measure := CALCULATE( [Collected_Premium], Claims[Country]="USA" ) ------------------------------ The final Pct measure would look like this: Pct Based on USA := VAR Country = [Collected_Premium] VAR USA = [USA Measure] RETURN DIVIDE(Country,USA)
@priyanshupahwa8886
Жыл бұрын
How can we do percent of row total for a viz
@NestorAdrianzen
Жыл бұрын
This tutorial might help. Cheers! kzitem.info/news/bejne/mn-arKVomoKpoI4
@jeffalex
9 ай бұрын
thank you... how about for % of column total?
@NestorAdrianzen
9 ай бұрын
Thanks for stopping by. This tutorial will help. Cheers! kzitem.info/news/bejne/woB8m4p8saqSlqQsi=W6f3N5WqPhCRXbEA
@hazeleyes322011
2 жыл бұрын
Hi Nestor, I am trying to get the % Online and I can't figure it out. . Please help! Customer Retail Online Total %Online A 20 100 120 83% B 50 200 250 80% C 75 100 175 57% D 10 100 110 91%
@NestorAdrianzen
2 жыл бұрын
hazeleyes322011, Thanks for stopping by. You might want to rearrange your data a little bit to perform a calculation. My recommendation would be to use Power Query to unpivot columns. The new table should have three columns: customer, channel and values. Then you can apply the concepts from the following tutorial. I hope that helps. kzitem.info/news/bejne/mn-arKVomoKpoI4
@jeremymorris-jarrett2111
3 жыл бұрын
What happens if you have more than 1 column? (like "sales of each product as % of total annual sales, over multiple years")
@NestorAdrianzen
3 жыл бұрын
Jeremy. Thanks for stopping by. When dealing with dates, you might want to create a Calendar table and connect it to the original table. If you want to have years as the column headers, you might want to use a Matrix instead of a table in your report. I hope this points you in the right direction. Cheers!
@maxenceaudibert7473
3 жыл бұрын
Hello Nestor, how can we calculate the % of total with only one decimal with a measure? Thank you
@NestorAdrianzen
3 жыл бұрын
Maxence, Thanks for stopping by. What you can do is the following: Select the created measure > Measure Tools > Select % > Type/select 1 in order to have just one decimal (this option is to the right of the % symbol). I hope it helps. Cheers!
@sravankumar1767
3 жыл бұрын
why should u use sumx function in percentages , can u explain it
@NestorAdrianzen
3 жыл бұрын
For this particular case, I used SUMX because it was easy to remove any filters from the claims table by using the ALL function. This DAX function works fine for small data sets, however for large data sets, I would use CALCULATE instead. Here is a tutorial about it as well. I hope it helps. Cheers! kzitem.info/news/bejne/mn-arKVomoKpoI4
@sravankumar1767
3 жыл бұрын
@@NestorAdrianzen Thank you for your explanation
@bzflowerbee
3 жыл бұрын
Thank you very much for all useful tutorials. I have a question about calculating the percentage. I have a payroll report that links to the payroll calendar table that I created. This payroll calendar has a payperiod number column based on the dates. So, on thereport,the report, I have categories such as overtime, sick time, vacation... . Now, it's easy for me to find out the % of each category. (Show value as percentage) However, i want the percentage per the payperiod not the grand total.That sounds complicated, isn't it ? Is there a way to do this type of calculation? Thank you so much! 🙏🙏
@NestorAdrianzen
3 жыл бұрын
bzflowerbee, Good questions. The CALCULATE function can definitely help to modify the filter context. Have you tried using the CALCULATE function with the ALLEXCEPT function together. This Video will help to use the CALCULATE function to find percentages. I hope this helps. kzitem.info/news/bejne/mn-arKVomoKpoI4
@bzflowerbee
3 жыл бұрын
@@NestorAdrianzen Excellent! This is exactly what I need! Again, thank you very much!🙏
@NestorAdrianzen
3 жыл бұрын
@@bzflowerbee Excellent!
@cc_sgyu
3 жыл бұрын
Thanks for the guide! Do you know how can we display, in the same table, both number values and percentages? For e.g i have one column on the absolute number, and the column beside it the percentage of grand total
@NestorAdrianzen
3 жыл бұрын
Hey Candy, Thanks for stopping by. When you mention about the absolute number, are you talking about the sum of collected premium? If so, you can just drag the premium column from the "fields" section into the table. I hope this answers your questions. Keep it up.
@mansijswarnkar4389
2 жыл бұрын
% week to week how to do that , please suggest
@NestorAdrianzen
Жыл бұрын
You might want to check this tutorial. For Week over Week, you can use as the NumberOfIntervals 7 and WEEK as the interval. kzitem.info/news/bejne/l6yAr3WAh4aVemU
@bvln555
3 жыл бұрын
Nice , but video quality is not good even playing in 720p
@NestorAdrianzen
3 жыл бұрын
Thanks for stopping by and for your feedback, Venkat Narayan.
@walidkellou1157
4 жыл бұрын
booooom
@NestorAdrianzen
4 жыл бұрын
Haha. Excellent, Walid Kellou. Hope you are enjoying the Power BI content!
@milesmoore5422
3 жыл бұрын
I never hit the "Like" button or subscribe to any channel until the end of the video. If the content is terrible. No LIKE !
@vinaykumarsingh8744
3 жыл бұрын
Row %
@NestorAdrianzen
3 жыл бұрын
Thanks for stopping by!
@giangdao9756
2 жыл бұрын
the sound is not good I am quite disappointed
@NestorAdrianzen
2 жыл бұрын
Sorry to hear that, Giang! In the meantime, I hope you found the content helpful.
@sunnyn1343
3 жыл бұрын
Very bad explanation can't understand much
@NestorAdrianzen
3 жыл бұрын
Sunny N, Thanks for stopping by and sorry I didn't meet your expectations!
Пікірлер: 123