Excellent! This is just perfect for the report that I have been working on. I have to calculate the overtime, unplanned hour, vacation for each locations. Thanks again!
@@NestorAdrianzen Everything messed up now when I filter it by pay period. So, I have a calendar table related to the payroll table that has all the category and location. So, I have period 1 to period 15 (these payperiod has calculation on it) Is that possible at all to add the date and filter it out to the payperiod I want? Thank you.
You can add as many fields as you want (not too many though) and keep a filter just for the first column (country column in the example) CALCULATE( [Total Profit], ALLEXCEPT(financials,financials[Country])
@azizdahduli, Thanks for stopping by. That's a good observation. When we use ALL and VALUES together in the CALCULATE function, VALUES evaluates the values of the product column as visible in the current filter context. In other words, ALL removes all filters in the financials table, but VALUES make the filters visible for the product column. When the product column is added to matrix, the percentages are computed for products as well and they add up to 100% for each product. I hope that helps!
If the column data is to shown with months, and If I write a measure for Percentage, then the percentage is showing for all the months, but I want to show only at the end, Any solution for this sir
@balajiawari, Thanks for stopping by. It seems that you have to play with the filter context. If you want to find the percentage of each row against the total amount, you might want to remove all the filters from the calendar table. The following expression might help. Measure := CALCULATE( [expression], REMOVEFILTERS(Dates) )
Hi Arianzen, i have watched this video a hundred times and I followed every step and things turned out to be exactly what I wanted. However, when I converted to the bar chart, everything turned to 100% for the Parent Row. Why? Can you tell me please? Thanks.
bzflowerbee, Thanks for your question. You might want to play with the allocation of the fields when you add data to your bar chart. Also be aware of the filter context (the fields visible if the bar chart are filters). My recommendation would be to create a "table" on the left side of the canvas and a "bar" chart on the right side. First, create the table with the results, once you got the results, duplicate it and change the visual type to a bar chart. Hopefully this make sense. Thanks!
I have a similar requirement. But my Product column is in another dimension table with 1 to Many join on the fact table. Following this, the calculation is being done based on grand total and not column total. Could you please help with that?
Sourav Sharma, Thanks for stopping by. You might want to play with the filter context in order to find the right results. Check this tutorial out if you haven't done it so. ruclips.net/video/9IdFq2aPxmY/видео.html
Hi Nestor, thanks for this nice video. I was wondering how one could do the Percentage formula as per group in a special scenario when the column is of String values. Example % of Name/Surname/Ticket No etc display as a trend? Any idea/suggestion would be a great help. Thanks
That's interesting! I'm not sure if that's possible. In order to perform calculations, you will need to have numeric values and then follow the steps provided in the tutorial.
Hello Nestor, im beginer for Powerbi. I have a quick question. In my report I have 17category 5countries and each countries has morethan 7supplier, I need to identify % of the supplier performed for each category, whic means my cal should be supplier value for each category and country / total calegory *100 how should i perform in powerbi ? Im using metrix table : Category, country and supplier ive taken in the column, values in row. Ex Category : sales. :Share 1 Chocolate. 10000. 100% India. 5000. 50% Sup A. 3000. 30% Supp B. 2000. 20% Srilanka. 2000. 20% Supp A. 1000. 10% Supp B. 1000. 10% Australia. 2000. 20% Supp A. 500. 5% Supp B. 1500. 15% NZ. 1000. 10% Supp A. 1000. 10% 2. Biscuit. 20000. 100% Turkey. 10000. 50% Supp A. 5000. 25% Supp B. 5000. 25% India. 10000. 50% Supp A. 6000. 30% Supp B. 4000. 20% This is wat i expect, kindly assist me
When you have more than 3 row levels, the calculation can be a little bit tricky. You'd just need to play with the filter context. e.g., one of the filters in the CALCULATE function could be REMOVEFILTERS (Table[country], Table[supplier]). Check out the tutorial at min 13:45. I hope that helps!
Hi Thanks for this. Need a DAX as per the below screenshot Name class Section Marks %age Dinesh X A 90. 90/90 Guru. IX. B. 50. 50/90 Hema. VII. C. 40. 40/90
Excelise, Thanks for stopping by. The approach to follow is very similar to what I shared in the tutorial. If the denominator is fixed, you might want to create a hardcoded measure by using variables. e.g., VAR fixedDenominator = 90. Then create another variable that sums the Marks column. Finally, divide hose values. Hopefully, that should solve your question.
Alonso, Gracias por revisar el contenido. El contenido en español usualmente lo estoy publicando en mi otro canal, vizualizando. Este es un tutorial muy parecido: ruclips.net/video/joU18fpDYs0/видео.html
Perfectly explained, Thank you
Thanks for stopping by, vamsi reddy. Kepp up the good work.
Excellent! This is just perfect for the report that I have been working on. I have to calculate the overtime, unplanned hour, vacation for each locations. Thanks again!
Glad you found it helpful. Keep it up!
@@NestorAdrianzen Everything messed up now when I filter it by pay period. So, I have a calendar table related to the payroll table that has all the category and location. So, I have period 1 to period 15 (these payperiod has calculation on it) Is that possible at all to add the date and filter it out to the payperiod I want? Thank you.
Thanks for the video. Is there a way to do Parent Row % not with only 1 level but with more? 3-4 levels?
You can add as many fields as you want (not too many though) and keep a filter just for the first column (country column in the example)
CALCULATE(
[Total Profit],
ALLEXCEPT(financials,financials[Country])
Thanks. Question. Could you add time columns on the side ?
Yes, you can! As long as you carefully modify the filter context within the CALCULATE function, you will be fine.
One of the simplest way to teach and reach the mind...
Glad to hear that. Cheers!
Is it possible to create a percent of parent row total in combination of new field parameters?
Why are you using the values function for the product column when that column isnt being used in the visualization, can you explain please. thank you
@azizdahduli,
Thanks for stopping by. That's a good observation.
When we use ALL and VALUES together in the CALCULATE function, VALUES evaluates the values of the product column as visible in the current filter context. In other words, ALL removes all filters in the financials table, but VALUES make the filters visible for the product column. When the product column is added to matrix, the percentages are computed for products as well and they add up to 100% for each product.
I hope that helps!
Thank you very much for the knowledge you have provided.
Glad you found the content helpful. Cheers!
Thank you so much!! I have been looking for this solution for such a long time & your direction gave me exactly what I needed :)
That's awesome! Thanks for stopping by, Kathy!
Thank you Nestor. Exactly what I needed.
Awesome! Thanks for your support 👍
Excellent tutorial Nestor, as usual. Thank you for your super explanations!
Thanks for your support, Iván. Keep it up!
If the column data is to shown with months, and If I write a measure for Percentage, then the percentage is showing for all the months, but I want to show only at the end, Any solution for this sir
@balajiawari,
Thanks for stopping by. It seems that you have to play with the filter context. If you want to find the percentage of each row against the total amount, you might want to remove all the filters from the calendar table. The following expression might help.
Measure :=
CALCULATE(
[expression],
REMOVEFILTERS(Dates)
)
Hi Arianzen, i have watched this video a hundred times and I followed every step and things turned out to be exactly what I wanted. However, when I converted to the bar chart, everything turned to 100% for the Parent Row. Why? Can you tell me please? Thanks.
bzflowerbee, Thanks for your question. You might want to play with the allocation of the fields when you add data to your bar chart. Also be aware of the filter context (the fields visible if the bar chart are filters).
My recommendation would be to create a "table" on the left side of the canvas and a "bar" chart on the right side. First, create the table with the results, once you got the results, duplicate it and change the visual type to a bar chart. Hopefully this make sense. Thanks!
I have a similar requirement. But my Product column is in another dimension table with 1 to Many join on the fact table.
Following this, the calculation is being done based on grand total and not column total. Could you please help with that?
Sourav Sharma,
Thanks for stopping by. You might want to play with the filter context in order to find the right results. Check this tutorial out if you haven't done it so. ruclips.net/video/9IdFq2aPxmY/видео.html
Hi Nestor, thanks for this nice video. I was wondering how one could do the Percentage formula as per group in a special scenario when the column is of String values. Example % of Name/Surname/Ticket No etc display as a trend? Any idea/suggestion would be a great help. Thanks
That's interesting! I'm not sure if that's possible. In order to perform calculations, you will need to have numeric values and then follow the steps provided in the tutorial.
Very good and clear tutorial, thanks
Glad to hear that. Cheers👍
Thanks a lot, Nestor!!!!!
Glad you found the content helpful. Cheers!
Hello Nestor, im beginer for Powerbi. I have a quick question.
In my report I have 17category 5countries and each countries has morethan 7supplier, I need to identify % of the supplier performed for each category, whic means my cal should be supplier value for each category and country / total calegory *100 how should i perform in powerbi ?
Im using metrix table : Category, country and supplier ive taken in the column, values in row.
Ex
Category : sales. :Share
1 Chocolate. 10000. 100%
India. 5000. 50%
Sup A. 3000. 30%
Supp B. 2000. 20%
Srilanka. 2000. 20%
Supp A. 1000. 10%
Supp B. 1000. 10%
Australia. 2000. 20%
Supp A. 500. 5%
Supp B. 1500. 15%
NZ. 1000. 10%
Supp A. 1000. 10%
2. Biscuit. 20000. 100%
Turkey. 10000. 50%
Supp A. 5000. 25%
Supp B. 5000. 25%
India. 10000. 50%
Supp A. 6000. 30%
Supp B. 4000. 20%
This is wat i expect, kindly assist me
When you have more than 3 row levels, the calculation can be a little bit tricky. You'd just need to play with the filter context. e.g., one of the filters in the CALCULATE function could be REMOVEFILTERS (Table[country], Table[supplier]). Check out the tutorial at min 13:45. I hope that helps!
Excellent, I m working on same type of project. 👍👍👍
Excellent! Glad to hear that. Keep up the good work, pavan ranapanga!
In question 2, why are you including the column product?
I have the same question too.
thank you
Glad you found the content helpful!
i cant download the practice file
Archie, Thanks for stopping by. Try this link: drive.google.com/file/d/1EBD5ibDOhVrdQFDWOrvbqjFKhdmU-tdV/view?usp=sharing
Great!
Thanks for stopping by, Sean Manzanilla. Keep up the good work.
👍
Excellent!
Hi Thanks for this. Need a DAX as per the below screenshot
Name class Section Marks %age
Dinesh X A 90. 90/90
Guru. IX. B. 50. 50/90
Hema. VII. C. 40. 40/90
Denominator is fixed as per the first row.
Excelise, Thanks for stopping by. The approach to follow is very similar to what I shared in the tutorial. If the denominator is fixed, you might want to create a hardcoded measure by using variables. e.g., VAR fixedDenominator = 90. Then create another variable that sums the Marks column. Finally, divide hose values. Hopefully, that should solve your question.
@@NestorAdrianzen thanks for this but I want to make denominator dynamic.Denominator will chnage every month.
Spanish?.
Alonso, Gracias por revisar el contenido. El contenido en español usualmente lo estoy publicando en mi otro canal, vizualizando. Este es un tutorial muy parecido: ruclips.net/video/joU18fpDYs0/видео.html