cancel
Showing results for
Did you mean:
Helper I

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

Hello All,

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.

Thank You

2 ACCEPTED SOLUTIONS
Helper I

I was able to achieve by creating new measures.

Helper I

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

4 REPLIES 4
Helper I

I was able to achieve by creating new measures.

Community Support

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

Best Regards,
Kelly

Helper I

@v-kelly-msft

Here is the new updated link,

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 %?

Helper I

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

Announcements