Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I have been working on model in which i am using a filled map to show the variance between current year and last year on the states. Everything is working fine but when i drop a data filed on to the legend column it is messing up my map colors
Here is an example:
Created a sample data in Excel (Didn't know how to attach the document so i am placing the values here)
state | date | cur_year | py_year |
az | 1/1/2017 | 20 | 15 |
az | 1/2/2017 | 5 | 6 |
az | 1/3/2017 | 10 | 20 |
az | 1/4/2017 | 25 | 35 |
az | 1/5/2017 | 100 | 90 |
ca | 1/1/2017 | 12 | 11 |
ca | 1/2/2017 | 11 | 12 |
ca | 1/3/2017 | 30 | 20 |
ca | 1/4/2017 | 5 | 15 |
ca | 1/5/2017 | 15 | 5 |
co | 1/1/2017 | 20 | 30 |
co | 1/2/2017 | 30 | 40 |
co | 1/3/2017 | 15 | 16 |
co | 1/4/2017 | 16 | 10 |
co | 1/5/2017 | 10 | 4 |
I uploaded this data in to Power BI and created 2 custom columns and i aslo created a date table with in the power BI and also unique States table
Custom Columns:
So finally this is how the model is looking before i drop the legend column
On the Map when we move the mouse to those states it will be showing the State name and teh average of "diff" column
For example for the state AZ : the average "diff" is -1.20 and the data colors should be between yellow to red
Data Colors :
Now when i drop the Legend column into the Legend filed then the colors are changing and the average values are also changing when i move the mouse to each state
After moving Legend into Legend column:
It showing the Avg Diff as -10.00 instead of -1.20.
And i also i found something intresting, right now i clicked only year but when i click a particular day then the legend is working fine.
Don't know why it creating a problem when i select for a compelte year/month when i drag the legend column into the legend field. I want to use the legend because when i click on neutral it should highlight those states where the Avg diff is between -3 and +3
Table relations
After commenting
Jared It didn't give me an option to attach the screenshot
Pelase find hte screenshot over here
Solved! Go to Solution.
Hey @Krish_76, so it is not possible to combine color saturation and a legend, because it introduces 2 conflicing uses of color. Power BI will use the legend settings and ignore the color saturation if you try to do both. You will need to choose between using color saturation without a legend, or using a custom color to create buckets, and setting the colors for those buckets.
For the second option, you would need to adjust the formulas slightly:
DiffColumn = CALCULATE ([Diff_test],ALLEXCEPT(MAP_Test,MAP_Test[state]))
Legend = IF ( MAP_Test[DiffColumn]>0, "Positive",IF(MAP_Test[DiffColumn] =0 ,"Neutral",IF(MAP_Test[DiffColumn]<0,"Negative")))
Then you can put Legend on the legend field. You can then change the data colors for your 3 legend values, so you could set Positive, Negative, and Neutral to the colors you want.
To give more granularity, you could consider doing buckets for ranges (0-5, 5-10, etc) rather than Pos/Neg/Neut
Hi @Krish_76, the issue is with your conditional column. For AZ, you have different values for the conditional column for each day. This is why the legend is not working until you select a day.
I think what you want is actually measure called Diff that calculates the average diff
Diff = AVERAGE ( [diff] )
You can then make a calculated column that calculates the Diff measure for each state across every day.
DiffColumn = CALCULATE ( Diff , ALL ( Day )
Finally, you can make your legend column as
Legend = IF ( DiffColumn > 0, "Positive", IF ( DiffColumn = 0, "Neutral", IF ( DiffColumn < 0, "Negative")))
Let me know if that works!
Can you post your .pbix file? It would be helpful to look at the data model. Can you share the calculation you are using for DiffColumn?
Hey @Krish_76, so it is not possible to combine color saturation and a legend, because it introduces 2 conflicing uses of color. Power BI will use the legend settings and ignore the color saturation if you try to do both. You will need to choose between using color saturation without a legend, or using a custom color to create buckets, and setting the colors for those buckets.
For the second option, you would need to adjust the formulas slightly:
DiffColumn = CALCULATE ([Diff_test],ALLEXCEPT(MAP_Test,MAP_Test[state]))
Legend = IF ( MAP_Test[DiffColumn]>0, "Positive",IF(MAP_Test[DiffColumn] =0 ,"Neutral",IF(MAP_Test[DiffColumn]<0,"Negative")))
Then you can put Legend on the legend field. You can then change the data colors for your 3 legend values, so you could set Positive, Negative, and Neutral to the colors you want.
To give more granularity, you could consider doing buckets for ranges (0-5, 5-10, etc) rather than Pos/Neg/Neut
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |