How to calculate percent & YTD percent using multiple slicers (filters)

[Anonymous]

Hello All,

https://drive.google.com/file/d/112XUWAetB2FCVNmum0qhLPaIuAD0tUFM/view?usp=sharing

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

Apr % = Apr # / App #

Bkr % = Bkr # / App #

Also, these 2 news rows are calcuated based on both the slicers.

Need your help.

Thank You

[Anonymous · Accepted solution]

I was able to achieve by creating new measures.

[Anonymous · Accepted solution]

I was able to achieve by creating new measures.

[v-kelly-msft]

Hi  @Anonymous,

The link you share can not be accessed,could you pls modify it with an available one?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

@v-kelly-msft 

Here is the new updated link,

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

Also, here is the sql sample 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

How to display additional ROW of Apr% & Bkr%, the calculations are:-
Apr% = Apr# / App#
Bkr% = Bkr# / App#

Also, how to display YTD for these 2 additional rows as %?

[Anonymous · Accepted solution]

I was able to achieve the solution by creating new measures to display as row in matrix visual.