Forum Discussion
sparkline in matrix
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.
Thanks!
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 TeamIf 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!
5 Replies
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 TeamIf 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!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,
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 TeamIf 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
Super User
ChihiroWa Try creating a measure using this
DAX
UnfilteredPopulation =
CALCULATE(
SUM('Population_total'[Population]),
ALL('Population_total'[Date])
)Thanks for the reply. Unfortunately nothing has changed. Is this the correct sparkline setting?
合計：sum total
