13 Replies Latest reply on May 4, 2018 11:35 AM by Jonathan May

# Percent of Total with filters using LODs?

I have a problem that I think an LOD can solve, but I'm getting tripped up. I have a data set of school districts' enrollment and I've built in ranking and filtering options for the purpose of a dashboard. But I'm having trouble with the labeling. I need the total number of students depending on the filters chosen (grade and/or student group) to show up on each bar. That part was easy. But I also need the percent of the total student population in the district that total represents. So for example, if I've selected American Indian for Grade 3, the total shows up for each district, but I need a percent of total that that population represents in each district.

I've tried an LOD, which I've called "Percent of Total": [Number]/{FIXED [District Name], [Grade] : SUM([Number])}

This gives me some crazy percents, so I know I'm not doing it right.

Any help would be greatly appreciated!

I've attached the workbook.

• ###### 1. Re: Percent of Total with filters using LODs?

Good morning

try this

it will return

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 2. Re: Percent of Total with filters using LODs?

Jim,

Thanks for the reply. Your solution worked part of the way. The only problem is that the percentages don't reflect the true percentages. So for example, in the screen shot you provided above, the American Indian population in Red Lake Public School District is actually 98% of the total student body (1,580/1,583).

Any thoughts as to why this is happening?

Thanks again!

• ###### 3. Re: Percent of Total with filters using LODs?

Hi Jonathan

I did this as a text table to better see what is going on

it will return this for the example you cited

here is what is goin on

the totals are in your student groups so the numbers were double counted (as least - filtering could really mess this up)

I created a new measure without the totals

then the percentage becomes

You may need to play with the LOD if you include other dimensions in the viz

Jim

1 of 1 people found this helpful
• ###### 4. Re: Percent of Total with filters using LODs?

Jim,

Thanks again for your work on this! I see that you can make it work in table format. And I'm able to replicate that, but...I can't seem to make it work with the user-driven filters like in the original.

I'm assuming it has to do with LODs agian, but when I play around with them, nothing changes (I get 100% for everything).

Any suggestions are greatly appreciated!

• ###### 5. Re: Percent of Total with filters using LODs?

Hi, Jonathan

Please find my solution attached. as well as below screenshot.

Hope this helps

ZZ

1 of 1 people found this helpful
• ###### 6. Re: Percent of Total with filters using LODs?

Zhouyi,

Thanks for replying! I think your solution get's me closer, but when I change the filters, the LOD is not accurate. For example, in your solution, when I change the filter from "Grade 3" to "All Grades", my example district "Red Lake Public School District" shows as having 11% American Indian students when it should be closer to 99%.

Any thoughts?

Thank you!

• ###### 7. Re: Percent of Total with filters using LODs?

see the attached

the percentage formula is :

{ INCLUDE [Student Groups],[District County Name],[Data Year]:

sum([JD cleaned numbers]) } /

{ FIXED  [District Name],[Data Year]:

sum([JD cleaned numbers]) }

and uses the numbers without totals

if NOT CONTAINS([Student Groups],'Total') then [Number] else 0 end

returns this

Jim

1 of 1 people found this helpful
• ###### 8. Re: Percent of Total with filters using LODs?

Hi,Jonathan

Not sure how you calculate the 99% in your sample, my calculation below shows 11%, Can you explain the numbers how you get to calculate?

ZZ

1 of 1 people found this helpful
• ###### 9. Re: Percent of Total with filters using LODs?

The "Total Students" represents the total population of the district. I think your total is double-counting a few things. The correct total for "Red Lake Public School District" is 1,583. So if the American Indian population is 1,580, that means they represent 99.8% of the Total Student number. Does that make sense?

• ###### 10. Re: Percent of Total with filters using LODs?

Hi, Jonathan

Please find my updated workbook attached and below is the results for your reference.

Hope this helps

ZZ

1 of 1 people found this helpful
• ###### 11. Re: Percent of Total with filters using LODs?

Jim,

This works great for most of the student groups (American Indian, Asian, Black, Hispanic, and White), but it doesn't work for some categories (Total Male, Total Female, Total Minority, and Total Students).

Do you know why this might be?

Also, I'm still not able to replicate this, even when I start with a blank worksheet. I'm not sure why this is...

• ###### 12. Re: Percent of Total with filters using LODs?

the issue you have is you have aggregated values (i.e. the totals) mixed with disaggregated values (the individual ethnic groups) so you are double counting people and it throws off all the percentages - (note the complexity in the dimensions makes it unclear (at least to me) the affect on filtered data) --

it doesn't work on the totals because the formula below specifically eliminates them

so what to do - you can recreate the same formulas except     if CONTAINS([Student Groups],'Total') then [Number] else 0 end

and build it into a Percentage calculation so you have 2 - I fot totals and one for ethnicities and put the 2 on a Dashboard

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 13. Re: Percent of Total with filters using LODs?

Zhouyi,

This worked! Thanks so much!

Big thanks to Jim for getting us a huge step closer!