Population pyramid Tornado chart with slicer for year - Power BI

Question

I am trying to build a population pyramid chart with custom visual Tornado chart. The population pyramid has data for 5 periods (2016, 2021, 2026, 2031, 2036). Once I built the pyramid I used a custom slicer "Play Axis" dynamic slicer. All good so far but by default the population pyramid is showing the total for all the 5 periods and when I use the Play Axis slicer it moves through each time period but at the end it comes back to the total of 5 periods.

I am wondering if it is possible to show the default view as 2016 in the chart and slicer rather than the total. Please see the screenshot and I have also included the example Power BI file for your reference.

https://1drv.ms/u/s!AubIV2PXG9p4gqo-jw3-DqUYuGORGA?e=9am2Uu

Any help is greatly appreciated.

mkRabbani mkRabbani · Accepted Answer · 2020-10-07T08:56:03

Create these following 3 measures-

erp_lowest_year = 

VAR min_year = MINX(ALL(Sheet1),Sheet1[Year])

RETURN 
CALCULATE(
    SUM(Sheet1[ERP]),
    FILTER(
        ALLEXCEPT(Sheet1,Sheet1[LGA],Sheet1[Agegroup],Sheet1[Sex]),
        Sheet1[Year] = min_year
    )
)

erp_sum = SUM(Sheet1[ERP])

erp_final = 
SWITCH(
    SELECTEDVALUE(Sheet1[Year]),
    BLANK(), [erp_lowest_year],
    [erp_sum]
)

Now, configure your Tornado chart as below-

This should work for you. You can also Download the updated report from HERE.

Population pyramid Tornado chart with slicer for year - Power BI

1 Answers