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
KhatiwadaR
Frequent Visitor

Filled Map/US State map with divergent color based on the student count

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?

 

KhatiwadaR_0-1652380565871.png

 

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

6 REPLIES 6
PhilipTreacy
Super User
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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


I have the color codes as: 

 
Color Legend (CF % Students by State) =
SWITCH(

TRUE(),

[Percent of Students by Program] <= 0.1, "#E4CCFF",

[Percent of Students by Program] <= 0.2, "#C8B8D8",

[Percent of Students by Program] <= 0.4, "#9071B1",
 
"#592A8A"
)
and I got the colors in the visual as expected:
KhatiwadaR_0-1653071011287.png

But I also want to have a Legend on the side of the visual like this: 

KhatiwadaR_2-1653071179617.png KhatiwadaR_1-1653071108381.png

 

Is there any way I can create such legend?
 
Thanks!

 

PhilipTreacy
Super User
Super User

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) 

 

 

map3.png

 

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


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:

KhatiwadaR_0-1652467446921.png

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

 

PhilipTreacy
Super User
Super User

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

filledmap.png

 

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

map1.png

 

and set the formatting like this

map2.png

 

regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


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?

 

IDPROGRAMEMPLOYERJOBTITLESTATESTATE_DESC
B001Nursing (BSN)AbcJob1NCNorth Carolina
B002Nursing (BSN)AbcJob1NCNorth Carolina
B003Nursing (BSN)AbcJob1NCNorth Carolina
B004Nursing (BSN)AbcJob1NCNorth Carolina
B005Nursing (BSN)AbcJob1NCNorth Carolina
B006Nursing (BSN)AbcJob1NCNorth Carolina
B007Management (BSBA)AbcJob1NCNorth Carolina
B008Management (BSBA)AbcJob2NCNorth Carolina
B009Management (BSBA)CdeJob2GAGeorgia
B010Management (BSBA)CdeJob2GAGeorgia
B011Management (BSBA)CdeJob2GAGeorgia
B012Nursing (BSN)CdeJob2GAGeorgia
B013Management (BSBA)CdeJob2GAGeorgia
B014Management (BSBA)CdeJob2GAGeorgia
B015Management (BSBA)CdeJob2GAGeorgia
B016Management (BSBA)CdeJob2VAVirginia
B017Nursing (BSN)CdeJob2GAGeorgia
B018Nursing (BSN)CdeJob2VAVirginia
B019Nursing (BSN)CdeJob2VAVirginia
B020Nursing (BSN)EfgJob3VAVirginia
B021Nursing (BSN)EfgJob4VAVirginia
B022Nursing (BSN)EfgJob5SCSouth Carolina
B023Management (BSBA)EfgJob6VAVirginia
B024Management (BSBA)EfgJob7VAVirginia
B025Management (BSBA)EfgJob8GAGeorgia
B026Management (BSBA)EfgJob3SCSouth Carolina
B027Management (BSBA)EfgJob4SCSouth Carolina
B028Nursing (BSN)EfgJob5SCSouth Carolina
B029Management (BSBA)EfgJob6SCSouth Carolina
B030Management (BSBA)EfgJob7SCSouth Carolina
B031Management (BSBA)EfgJob7SCSouth Carolina
B032Management (BSBA)EfgJob8GAGeorgia
B033Management (BSBA)EfgJob8GAGeorgia
B034Management (BSBA)EfgJob8GAGeorgia

 

I want to color as below:

0-10% - extra light purple

11-30%- light purple

31-50% - purple

51% - 100% - dark purple 

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.