cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Caitlin_Knox
Advocate III
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]

1 ACCEPTED SOLUTION
Mykhael
Kudo Kingpin
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

View solution in original post

7 REPLIES 7
Phil_Seamark
Microsoft
Microsoft

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.

 

https://powerbi.microsoft.com/en-us/blog/power-bi-update-lots-of-new-authoring-features-and-a-new-po...

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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
Kudo Kingpin
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

Thank you so much for taking the time to make that helpful video. When I applied it to my data, it worked perfectly

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

 

Thanks Miguel!

Mykhael
Kudo Kingpin
Kudo Kingpin

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. mmyers@redwaves.onmicrosoft.com 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

 

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors