Skip to main content

Modifying the model

Adding new measures.

This is a simple dashboard, just one filter (census survey year) and  few calculated measures.

The measures are:
  • Total Population
  • Previous Year Population

  • Change Percent
  • Population Change
  • Percent of US Population

also, there are two calculated measures to format and add additional information to the dashboard.
  • Value color (Green if current year population is greater than previous year population otherwise Red)
  • Most populated counties chart header (to show selected states)

To add a new measure, right-click on the US Population ACS5 table and select New Measure


the add the formula for total_population and select the thousands separator format


Repeat this process to add

prev_census_tot_pop =
VAR PreviousCensusYear =
    MAX ( 'US Population ACS5'[census_year] ) - 1
RETURN
    CALCULATE (
        SUM ( 'US Population ACS5'[total_pop] ),
        'US Population ACS5'[census_year] = PreviousCensusYear
    )

pop_change =
'US Population ACS5'[total_population] - 'US Population ACS5'[prev_census_tot_pop]


change_pct =
1 - DIVIDE ( [prev_census_tot_pop], [total_population] )
change_pct = 1-DIVIDE([prev_census_tot_pop],[total_population])


pop_pct =
DIVIDE ( [total_population], CALCULATE ( [total_population], ALLSELECTED () ) )



for the "formatting" measures.



color_pop_change =
IF (
    'US Population ACS5'[total_population] > [prev_census_tot_pop],
    "Green",
    "Red"
)

chart_header =
VAR SelectedStates =
    VALUES ( States[state_name] )
VAR NumOfStates =
    COUNTROWS ( Selectedstates )
VAR NumberOfAllStates =
    COUNTROWS ( ALL ( States[state_name] ) )
VAR AllStatesSelected = NumOfStates = NumberOfAllStates
VAR StatesNames =
    CONCATENATEX ( states, States[state_name], ", ", States[state_name], ASC )
VAR Result =
    IF (
        AllStatesSelected,
        "",
        IF (
            NumOfStates > 5,
            UNICHAR ( 10 ) & "(More than 5 States selected, see map or table for details.)",
            UNICHAR ( 10 ) & "(" & StatesNames & ")"
        )
    )
RETURN
    "Top 10 most populated counties" & Result



Is a good idea to add folders to organize the fields.

In the model, select one of the new measures and in the folder field type Measures.




Drag and drop the other measures in to folder.

Repeat the process for the format measures, creating a new folder called "Formatting".

The end result will look like this.




Now with the model completed we can start building the dashboard.

Comments

Post a Comment

Popular posts from this blog

Building the dashboard (Part 1)

To show how to create the dashboard I divided the canvas in 3 sections I going to dedicate one post for every section, and the another 2 posts for the tooltip and drill through   Adding Census Year slicer First we are going to add the census year filter. This filter will affect all visual in our dashboard. Select the slicer visual and the census_year field from the US Population ACS5 table Before diving into specifics formats, disable/enable the following formatting options to match the bellow image. Now in the selection control section change to Single select   Change the Text size, Alignment, and Color for Items,  Title and Border sections The final result Adding the Cards Select the Card visual and drag and drop total_population measure into the Fields box Rename the Fields from total_population → Total Population The formatting options are: In the Data label section, change...
Post a Comment
Read more

Building the dashboard (Part 2)

Adding the switch button Section two is a little more complicated.  We are going to use two visual (Shape Map and Table) and four images to switch between those visuals. To add the images select, Insert -> Image For now keep the switch on and off  images apart and with the same size. The Map visual In the the US map we want to show the State color intensity proportional to the population of the census year. Select the Shape Map visualization If this option is not available in your Visualization bar, update Power BI desktop to the most current version or look for Shape Map in the preview features option. For the Location drag and drop the state_name column from the States table. For the Color Saturation select the total_population measure   Finally, rename the selected visualization  columns state_name-> State, total_population-> Population. The Table visual It is important  to keep the Map visual and the Table visual apart, in the same way we did with ...
Post a Comment
Read more