How to add new row to the matrix visual for each Month & YTD %?

[Anonymous]

Hello All,

https://drive.google.com/file/d/1QcQefctJr36mB45uwi_98guZ8Vqc-GDb/view?usp=sharing

I want to Add "Apr %" & "Bkr %" rows to the above matrix visual based on calculation:-

Apr % = Apr # / App #

Bkr % = Bkr # / App #

These 2 news rows are calcuated based on both the slicers.
Also, how to display YTD for these 2 additional rows as %?

Need your help.

Thank You

[Anonymous]

Here is the sample sql data:-

--CREATE TABLE dbo.vw ( app_dt date, borr_nm varchar(10), mt varchar(10), mtvalue smallint )

--INSERT INTO dbo.vw VALUES ( '2020-11-01', 'Fr', 'App #',  14 )
--INSERT INTO dbo.vw VALUES ( '2020-11-01', 'Fr', 'Apr #',  9 )
----INSERT INTO dbo.vw VALUES ( '2020-11-01', 'Fr', 'Apr %',  64 )
--INSERT INTO dbo.vw VALUES ( '2020-11-01', 'Fr', 'Bkr #',  9 )
----INSERT INTO dbo.vw VALUES ( '2020-11-01', 'Fr', 'Bkr %',  64 )

--INSERT INTO dbo.vw VALUES ( '2020-11-01', 'Nw', 'App #',  92 )
--INSERT INTO dbo.vw VALUES ( '2020-11-01', 'Nw', 'Apr #',  0 )
----INSERT INTO dbo.vw VALUES ( '2020-11-01', 'Nw', 'Apr %',  0 )
--INSERT INTO dbo.vw VALUES ( '2020-11-01', 'Nw', 'Bkr #',  0 )
----INSERT INTO dbo.vw VALUES ( '2020-11-01', 'Nw', 'Bkr %',  0 )

--INSERT INTO dbo.vw VALUES ( '2020-11-01', 'Pr', 'App #',  65 )
--INSERT INTO dbo.vw VALUES ( '2020-11-01', 'Pr', 'Apr #',  49 )
----INSERT INTO dbo.vw VALUES ( '2020-11-01', 'Pr', 'Apr %',  75 )
--INSERT INTO dbo.vw VALUES ( '2020-11-01', 'Pr', 'Bkr #',  50 )
----INSERT INTO dbo.vw VALUES ( '2020-11-01', 'Nw', 'Bkr %',  76 )


--INSERT INTO dbo.vw VALUES ( '2020-12-01', 'Fr', 'App #',  13 )
--INSERT INTO dbo.vw VALUES ( '2020-12-01', 'Fr', 'Apr #',  7 )
----INSERT INTO dbo.vw VALUES ( '2020-12-01', 'Fr', 'Apr %',  53 )
--INSERT INTO dbo.vw VALUES ( '2020-12-01', 'Fr', 'Bkr #',  7 )
----INSERT INTO dbo.vw VALUES ( '2020-12-01', 'Fr', 'Bkr %',  53 )

--INSERT INTO dbo.vw VALUES ( '2020-12-01', 'Nw', 'App #',  499 )
--INSERT INTO dbo.vw VALUES ( '2020-12-01', 'Nw', 'Apr #',  0 )
----INSERT INTO dbo.vw VALUES ( '2020-12-01', 'Nw', 'Apr %',  0 )
--INSERT INTO dbo.vw VALUES ( '2020-12-01', 'Nw', 'Bkr #',  0 )
----INSERT INTO dbo.vw VALUES ( '2020-12-01', 'Nw', 'Bkr %',  0 )

--INSERT INTO dbo.vw VALUES ( '2020-12-01', 'Pr', 'App #',  386 )
--INSERT INTO dbo.vw VALUES ( '2020-12-01', 'Pr', 'Apr #',  374 )
----INSERT INTO dbo.vw VALUES ( '2020-12-01', 'Pr', 'Apr %',  96 )
--INSERT INTO dbo.vw VALUES ( '2020-12-01', 'Pr', 'Bkr #',  379 )
----INSERT INTO dbo.vw VALUES ( '2020-12-01', 'Pr', 'Bkr %',  98 )

SELECT	*
FROM	dbo.vw

--DROP TABLE dbo.vw

[amitchandak · Accepted solution]

@Anonymous , You have to create a measure and use the show on row. Please find the file attached with changes. Also, use date table for datesytd

[Anonymous]

Thank you very much! @amitchandak