How to add only one column to matrix table to show trend line base on all other column values?

[elliejiang20]

How to add only one column like below picture1) to matrix table In Power BI to show a trend line base on all other columns single values?
Require:
1) Red peak point on Trend column are the values from Sep'22(99.75%), Oct'22, Nov'22' Dec'22, Jan'23, Feb' 23. 

2) I use 'Add SparkLine' in PBI, the visual with trend like below, the trend column added to every column. it is not correct.

3) I also use 'Add SparkLine' in PBI and do an aggregation : Sum of TotalSales, then the SparkLine show up as per aggregation date, it is also not correct. Because I want sparkline show up base on every month percentage value which is not an aggregation value like Sum of..., but all single values of percentage.

I didn't find out which visual provided outside of PBI neither.

----Can use below data for testing---
CREATE TABLE [dbo].[TableName](
[State] [nchar](10) NULL,
[Date] [date] NULL,
[TotalSales] [int] NULL
) ON [PRIMARY]

INSERT INTO [db name].[dbo].[tableName]
Values
('WA','2022-01-01',70),
('WA','2022-02-01',70),
('WA','2022-03-01',50),
('WA','2022-04-01',60),
('WA','2022-05-01',80),
('WA','2022-07-03',90),
('WA','2022-08-04',100),
('WA','2022-10-05',20),
('WA','2022-11-06',90),
('CA','2022-01-01',60),
('CA','2022-02-01',60),
('CA','2022-03-01',80),
('CA','2022-04-01',20),
('CA','2022-05-01',80),
('CA','2022-07-03',20),
('CA','2022-08-04',50),
('CA','2022-10-05',40),
('CA','2022-11-06',80),
('CA','2022-01-09',160),
('CA','2022-02-09',160),
('CA','2022-03-09',180),
('CA','2022-04-10',120),
('CA','2022-05-10',180),
('CA','2022-07-09',120),
('CA','2022-08-07',150),
('CA','2022-10-11',140),
('CA','2022-11-13',180）

Please help with this, thank you.

[Anonymous]

Hi @elliejiang20 

1.If you want to only display the only leave sparkline column and other columns. you need to remove the column name from the columns", otherwise, it will display the related value for each column value.

Then you can create four measure to for 4 quarters and then put them to the visual field

e.g 

Q1 = CALCULATE(SUM('Table (2)'[Column3]),QUARTER('Table (2)'[Column2])=1)
Q2 = CALCULATE(SUM('Table (2)'[Column3]),QUARTER('Table (2)'[Column2])=2)

2.Then you can create a measure to put it to the y-axis of sparkline column

e.g 

Measure 3 = DIVIDE(CALCULATE(SUM('Table (2)'[Column3]),QUARTER('Table (2)'[Column2])=SELECTEDVALUE('Table (2)'[Column2].[QuarterNo])),CALCULATE(SUM('Table (2)'[Column3]),ALLEXCEPT('Table (2)','Table (2)'[Column1])))

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[elliejiang20]

Move forwarded, still use sparkline, now don't want highlight columns below, how to remove it, only leave sparkline column and other columns?