Forum Discussion
Advocate III% of Total
I've read through the other posts on this topic, but can't seem to apply it to my model.
In the same table, I have multiple Groups- each with their own code. I've created a calculated column that parses out the Check amount for that Group. I'd like to call this column into my %of total calculation so that I can get the % of total of a department against the Group total.
Here's what I have so far
% = DIVIDE(SUM(vw_ClientMovements[Check Amounts - S2458]),CALCULATE(SUM(vw_ClientMovements[Check Amounts - S2458]),ALLSELECTED(vw_ClientMovements[Expenses - S2458 - Bingo/Casino])))
[Check Amounts - S2458] is the calcluated column that only has CHK_AMOUNTs for Group S2458
[Expenses - S2458 - Bingo/Casino] is a calculated column that only returns CHK_AMOUNTs if 2 conditions are met- being part of Group S2458 and being in the department, "Bingo/Casino"
When I have %ofTotal as a calculated column, it just returns 1. When I have it as a measure, I get the error message "A single value for column 'CHK_AMOUNT' in table 'vw_ClientMovements' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
I have gotten this to work- I just think the above is a better way to do it.
Bingo/Casino % Total = [Sum of Expenses - S2458 - Bingo/Casino]/[Total w/out Fixed Cost - S2458]
Hello Caitlin_Knox
I uploaded a video to show you how I can get % of Total using my data.
https://www.youtube.com/watch?v=I5n_VvDoxvE&feature=youtu.be
Best
Miguel Myers
7 Replies
- Mykhael
Kudo Kingpin
Hello Caitlin_Knox
I uploaded a video to show you how I can get % of Total using my data.
https://www.youtube.com/watch?v=I5n_VvDoxvE&feature=youtu.be
Best
Miguel Myers
Caitlin_KnoxThank you so much for taking the time to make that helpful video. When I applied it to my data, it worked perfectly
- Mykhael
I am glad it was helpful. If you have another question let me know, I will do my best to assist you. Have a Great day.
Miguel Myers
- Phil_Seamark
Microsoft Employee
You might also take advantage of a new feature of Power BI Desktop that has been released today.
Open this link and scroll to the bottom to learn about 1-click Percentage Transform.
- Caitlin_Knox
I looked at this new update, but I don't think it has the capacity for % of Total function. From what I saw, it only does xx% of whatever value you choose. Thanks for the suggestion though
- Mykhael
Hello Caitlin_Knox,
I can see the problem, but if you can send me part of your data, I can send you screenshots showing you step by step how you can fix the issue with your formulas and get the result you are looking for. [email protected] is my email.
If you can't I will give you an idea How I can get the % of total from one of my data sets.
Greetings
Mykhael
