Two issues: Relationship between tables and shape map dynamic fill colors. Need Help.

[gaylagarrett]

I have been stuck on this for days/weeks, so any assistance will be greatly appreciated.  I greatly appreciate any assistance.  I tried to upload the .pbix file to Google Drive, but haven't been able to do so successfully. Here is a link in case it works: 

Data description.  I have two source data tables:

Issues Reported Table: Records for issues reported on a tshirt product with the following 6 columns:  ID (unique), Issue Reported Date, State Name (United States), Size, and Color.

Active Accounts Table: Records for volume of Active Accounts by State and Month/Year with the following 3 columns:  MM/DD/YYYY (in date format as first day of period month), State Code (United States), Active Accounts Volume.)

And I have two tables I created to help solve the problem: a date table & a State dimensions table.  These can be changed as needed to solve the issue. 

I need to create a shade map that shows the relationship between the Number of Issues Reported and the Number of Active Accounts by State, for trend analysis.  I don't want to shade by volume of issues because, for example, if there were 5 reported issues for only 5 active accounts in a state,  this would be considered a bigger problem then if there were 5 reported issues for 100 active accounts in a state. So I need to do a ratio of Number of Issues reported by Number of Active Accounts.   The Number of Active Accounts for each state fluctuates every month,  so I need to use the average of Active Accounts volume as the denominator of the ratio and the count of the issues reported as the numerator of the ratio.

Measures created:

• Active Accounts Average = AVERAGE('Active Accounts by State & MonthYear'[Active Accounts Volume])
• Issue Volume = CALCULATE( COUNT ( 'Issues Reported'[ID] ) )
• Issues to Volume Ratio = CALCULATE(DIVIDE([Issue Volume], [Active Accounts Average]) * 1000)

Two issues: 

1. I am having difficulty creating the relationship between the tables to create the ratio accurately, since the Active Accounts data is aggregated volume of active accounts monthly by state and the Issues Reported is a line by line log of each individual issue.   As I have it now in the dashboard attached,  I created a date table and a State Dimension table to create relationships between the Active Accounts and Issues Reported table but I don't think it is set up correctly.
2. Shape Map color saturation uses the Issues to Volume Ratio. The shape map fill colors section in the Format Visual require static numbers for the Minimum, Center, and Maximum gradient color fields.  Since the shading is based upon the Issues to Volume Ratio,  this number can change drastically depending on filters selected and there is not one size fits all numbers that work for shading the map of states in relation to each other.  I've tried a few things, but ultimately what I need to do is create a normalized value measure based upon what the minimum and maximum ratios are for the filters selected.  In the first screenshot below, the gradient values are set to 1, 25, 50 based upon all the ratios with no filters.  But if I select filters, such as Red color & X-Large size,  the ratio values drop and there is not a big shading difference between states anymore. I need the shading to dynamically match the "ranking" of the ratio values with filters applied.  I've done some due diligence on searching the forums,  I've tried different methods found online,  for example, creating a Min and Max and normalizing the data within those, but the Min and Max don't seem to work properly on my ratio measure.  

Shape Map with no filter selected

Shape Map with X-Large size and Red Color filters selected

[Anonymous]

Hi @gaylagarrett ,

Regarding your first question, what is your requirement? To filter the number of questions and active users in the same month by month?

Regarding your second question, I found that the visual can only set static values. There should be an option fx for conditional formatting.I think the visual you are using now does not support your needs.Do you accept to change the visual object to 'matrix'?

[gaylagarrett]

 v-zhouwen-msft - 

On the first question, I need to be able to calculate the ratio of issue volume by the average active accounts for a time period,  in year increments.  The date filter will be by year.   

On the second question,  I want a map that shows by color fill what states have the highest ratio of issue volume to active accounts for a time period. The shape map does not have the function/fx capability, but I think that there could be a way to create a measure that dynamically forces the "ranking" of issue volume to active accounts ratio to a normalized number (between 1 and 2 maybe), and then use that measure as the color fill value.  So out of 50 states,  no matter what filters are selected they would have a ratio normalized between the numbers 1 and 2 (1.1, 1.2, 1.6, etc.) and then I can select 1 as the minimum color value, 1.5 as the center and 2 as the maximum.

[gaylagarrett · Accepted solution]

The Shape Map is a preview item, and I guess there are some bugs with it still as there is not a function option on the fill colors.  I did find that if I create the map as a filled map with a function for the fill colors,  I can THEN change it to a Shape Map and the function option will remain.  However, it doesn't work if you create the Shape Map, change it to a Filled Map, use the function option and then change it back to a Shape Map. You have to start it as a Shape Map.