Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
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.

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.