sparkline in matrix

[ChihiroWa]

Hello. I hope someone can help me with my problem.

This is a matrix of population data by country sourced from Worldbank.

Since 2023 is selected in the slicer for selecting the year, the numbers in the second column show the population count for that year. So far so good, but I am having trouble with the third column showing only dots on the sparkline.

I have tried adding new columns to the data table that do not affect the slicer, but it does not work.

Unfiltered Date = CALCULATE(MAX('Population_total'[Date]), ALL('Population_total'))

Thanks!

[v-huijiey-msft]

Hi @ChihiroWa ,

Thanks for the reply from bhanu_gautam .

I can reproduce your problem.

Here's my test data:

Country	Population	Date
Country A	500	2020-01-01
Country A	510	2021-01-01
Country A	520	2022-01-01
Country A	530	2023-01-01
Country B	600	2020-01-01
Country B	610	2021-01-01
Country B	620	2022-01-01
Country B	730	2023-01-01
Country C	600	2020-01-01
Country C	720	2021-01-01
Country C	710	2022-01-01
Country C	730	2023-01-01

To solve this problem follow my lead:

First, create a calculated column to extract the year of the date, drag the Year column to the slicer to filter it.

Year = YEAR('Population_total'[Date])

Please create a measure:

UnfilteredPopulation = 
CALCULATE(
    SUM('Population_total'[Population]),
    ALL('Population_total'[Year])
)

Create a matrix visual object by dragging Country to Rows and Population to Values.

Create a sparkline as shown below, Use the created measure as the Y-axis and select “Year” under the date hierarchy as the X-axis. (Note: do not select the date, do not select the created calculation column Year as the X axis)

The final visual effect is shown below:

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[ChihiroWa]

Thanks alot for your help!

I understand your explanation very well. However, in my report, multiple tables are connected to the calendar table as shown below. In this case, the method you have given me does not work. How should I handle this?

Thanks,

[v-huijiey-msft]

Hi @ChihiroWa ,

Okay, I reproduced your situation.

To solve this problem, simply use “Year” as the X-axis under the date hierarchy of the “Calendar” table instead of “Population_total”. (Note: Do not select the date and do not select the created calculated column Year as the X-axis)

The final page visualization is shown below:

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[bhanu_gautam]

@ChihiroWa Try creating a measure using this

DAX
UnfilteredPopulation =
CALCULATE(
SUM('Population_total'[Population]),
ALL('Population_total'[Date])
)

[ChihiroWa]

Thanks for the reply. Unfortunately nothing has changed. Is this the correct sparkline setting?

合計：sum total