Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have student's post graduation employment data with the Programs students graduated in and employer, job title, and the State where they got job. I want to make Program as a Slicer and when I select a program, I want to show in the filled map (State) with divergent color based on what % of Total students are in the state.
For example,
Lets say there are 10 graduates from Nursing Program. Out of the 10, if 40% or less students are in SC or GA or VA, I want to color them as Light Purple and if 41% to 80% are in NC or TX then I want to color NC or TX as Purple, and if 81% or above in FL then I want to color FL as Dark Purple. How can i do it in Power Bi?
Solved! Go to Solution.
Hi @KhatiwadaR
To exclude students who have not reported their work location you could use this
Total Students = CALCULATE(COUNTROWS('DataTable'),FILTER(ALL('DataTable'), 'DataTable'[STATE] <> ""))
NOTE: In my sample data the blanks are actually empty strings "". If your data actually contains BLANK values you will need to test for that e.g. <> BLANK()
If you want to get the total number of students per program, excluding blank states use this
Total Students by Program = CALCULATE(COUNTROWS('DataTable'),FILTER(ALL('DataTable'), 'DataTable'[STATE] <> "" && 'DataTable'[PROGRAM] = SELECTEDVALUE('DataTable'[PROGRAM])))
So the actual percentages you want are given by this
% of Students by Program = DIVIDE([Students in State by Program] , [Total Students by Program])
Download the sample PBIX file for all of this code.
regards
Phil
Proud to be a Super User!
Hi @KhatiwadaR
To exclude students who have not reported their work location you could use this
Total Students = CALCULATE(COUNTROWS('DataTable'),FILTER(ALL('DataTable'), 'DataTable'[STATE] <> ""))
NOTE: In my sample data the blanks are actually empty strings "". If your data actually contains BLANK values you will need to test for that e.g. <> BLANK()
If you want to get the total number of students per program, excluding blank states use this
Total Students by Program = CALCULATE(COUNTROWS('DataTable'),FILTER(ALL('DataTable'), 'DataTable'[STATE] <> "" && 'DataTable'[PROGRAM] = SELECTEDVALUE('DataTable'[PROGRAM])))
So the actual percentages you want are given by this
% of Students by Program = DIVIDE([Students in State by Program] , [Total Students by Program])
Download the sample PBIX file for all of this code.
regards
Phil
Proud to be a Super User!
I have the color codes as:
But I also want to have a Legend on the side of the visual like this:
Is there any way I can create such legend?
Thanks @KhatiwadaR
I think I can work with that data but I'm still not 100% clear on how you calculate your % of programs per state. Students in the state / Total of Students for All Programs?
Please download this PBIX file and see if it's what you want.
It's easy to modify it if the calcs need adjusting.
You can choose the Hex color codes for hat you want here Color Hex Color Codes (color-hex.com)
Regards
Phil
Proud to be a Super User!
Thank you, Phil.
I think we are very much close to the solution. I want Students in State in a Program/ Total students in that Selected Program but not in all programs. I think I could twik that code, but I got another issue. I have some 'Blank' rows in state field (students did not report their work location). I want to exclude them from the % calculation.
Below I have attached a snippet:
I want to exclude 133 and calculate only based on the 17 Total count. so NC will be 11/17*100% = 65% and VA will be 2/17*100 = 12% and so on.
Thanks,
Ram
Hi @KhatiwadaR
Can you please explain in detail how you work out the distribution of students by state?
If you have 10 students and 40% or less are in SC, GA and VA then let's say that's 1 student per state = 3.
If 41% to 80% of sudents are in NC or TX then that's at least 5 students per state and you've only got 10 students in total?
If you want to do some aggregation of states before assigning color how do you decide to do that?
For example, if TX has 50% of students then you'd color it Light Purple. But if you add TX and NC (which let's say has 40% of sudents) then are they both colored Dark Purple?
The logic/rules behind deciding what colors to assign are not clear.
Can you please provide some sample data?
All that said, you can use Conditional Formatting on the Filled Map. You need to write a measure to assign the colors according to the values. Colors can be Hex (as below), known CSS color names, RGB or HLSA values : more info here CSS Color Module Level 3 (w3.org)
Download sample PBIX file with the code and map shown below
Here's the example measure I wrote
CF Students by State =
VAR _students = SELECTEDVALUE(Data[Students])
RETURN
SWITCH(
TRUE(),
_students < 0.4, "#F3B4DC",
_students < 0.8, "#A666B0",
"#893395"
)
Giving this
To assign the Conditional Formatting to the map, click on the map and then under Format Visual, open up Fill Colors and click on the fx button
and set the formatting like this
regards
Phil
Proud to be a Super User!
Hello Philip,
I could not attach the pbix file. Your email did not work. Can you please suggest me how to upload the file?
ID | PROGRAM | EMPLOYER | JOBTITLE | STATE | STATE_DESC |
B001 | Nursing (BSN) | Abc | Job1 | NC | North Carolina |
B002 | Nursing (BSN) | Abc | Job1 | NC | North Carolina |
B003 | Nursing (BSN) | Abc | Job1 | NC | North Carolina |
B004 | Nursing (BSN) | Abc | Job1 | NC | North Carolina |
B005 | Nursing (BSN) | Abc | Job1 | NC | North Carolina |
B006 | Nursing (BSN) | Abc | Job1 | NC | North Carolina |
B007 | Management (BSBA) | Abc | Job1 | NC | North Carolina |
B008 | Management (BSBA) | Abc | Job2 | NC | North Carolina |
B009 | Management (BSBA) | Cde | Job2 | GA | Georgia |
B010 | Management (BSBA) | Cde | Job2 | GA | Georgia |
B011 | Management (BSBA) | Cde | Job2 | GA | Georgia |
B012 | Nursing (BSN) | Cde | Job2 | GA | Georgia |
B013 | Management (BSBA) | Cde | Job2 | GA | Georgia |
B014 | Management (BSBA) | Cde | Job2 | GA | Georgia |
B015 | Management (BSBA) | Cde | Job2 | GA | Georgia |
B016 | Management (BSBA) | Cde | Job2 | VA | Virginia |
B017 | Nursing (BSN) | Cde | Job2 | GA | Georgia |
B018 | Nursing (BSN) | Cde | Job2 | VA | Virginia |
B019 | Nursing (BSN) | Cde | Job2 | VA | Virginia |
B020 | Nursing (BSN) | Efg | Job3 | VA | Virginia |
B021 | Nursing (BSN) | Efg | Job4 | VA | Virginia |
B022 | Nursing (BSN) | Efg | Job5 | SC | South Carolina |
B023 | Management (BSBA) | Efg | Job6 | VA | Virginia |
B024 | Management (BSBA) | Efg | Job7 | VA | Virginia |
B025 | Management (BSBA) | Efg | Job8 | GA | Georgia |
B026 | Management (BSBA) | Efg | Job3 | SC | South Carolina |
B027 | Management (BSBA) | Efg | Job4 | SC | South Carolina |
B028 | Nursing (BSN) | Efg | Job5 | SC | South Carolina |
B029 | Management (BSBA) | Efg | Job6 | SC | South Carolina |
B030 | Management (BSBA) | Efg | Job7 | SC | South Carolina |
B031 | Management (BSBA) | Efg | Job7 | SC | South Carolina |
B032 | Management (BSBA) | Efg | Job8 | GA | Georgia |
B033 | Management (BSBA) | Efg | Job8 | GA | Georgia |
B034 | Management (BSBA) | Efg | Job8 | GA | Georgia |
I want to color as below:
0-10% - extra light purple
11-30%- light purple
31-50% - purple
51% - 100% - dark purple
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |