Create Dual Axis Chart with few modifications

[Anonymous]

What to create Dual Axis chart - With a filter Showing data for Just one year.

Columns Showing the data for # Touched - For the Current Year as well as Prior Year i.e. if we select 2018 using filter for Year field, columns should show data for 2017 and 2018.

Line Chart Should show %Eligible.

%Eligible can be calculated as - Touched by Orig_Qualified.

Attached is data for reference. Maily we need to place #Touch one year back in each row. 

Any help is much appreciated. 

S_NO	Year	Month_Per_Yr	Orig_Qualified	Touched
1	2011	1/1/2011	6,194	2,012
2	2011	2/1/2011	5,715	1,592
3	2011	3/1/2011	7,266	4,090
4	2011	4/1/2011	5,268	2,856
5	2011	5/1/2011	4,417	2,096
6	2011	6/1/2011	4,325	1,935
7	2011	7/1/2011	5,063	2,524
8	2011	8/1/2011	3,698	1,747
9	2011	9/1/2011	3,504	1,807
10	2011	10/1/2011	5,599	3,710
11	2011	11/1/2011	6,547	1,876
12	2011	12/1/2011	8,674	2,293
1	2012	1/1/2012	8,612	2,623
2	2012	2/1/2012	8,991	3,603
3	2012	3/1/2012	7,291	1,833
4	2012	4/1/2012	6,749	1,655
5	2012	5/1/2012	8,006	2,372
6	2012	6/1/2012	6,303	1,714
7	2012	7/1/2012	5,789	2,008
8	2012	8/1/2012	5,976	3,230
9	2012	9/1/2012	4,318	1,666
10	2012	10/1/2012	4,761	1,686
11	2012	11/1/2012	5,607	1,847
12	2012	12/1/2012	7,687	1,951
1	2013	1/1/2013	9,061	2,213
2	2013	2/1/2013	10,361	3,969
3	2013	3/1/2013	8,302	2,234
4	2013	4/1/2013	10,037	2,239
5	2013	5/1/2013	9,867	2,744
6	2013	6/1/2013	8,587	2,139
7	2013	7/1/2013	8,060	1,956
8	2013	8/1/2013	11,142	3,911
9	2013	9/1/2013	11,510	3,621
10	2013	10/1/2013	12,967	3,746
11	2013	11/1/2013	12,682	3,585
12	2013	12/1/2013	12,565	3,560
1	2014	1/1/2014	12,379	3,342
2	2014	2/1/2014	13,144	3,997
3	2014	3/1/2014	11,383	2,228
4	2014	4/1/2014	10,302	2,108
5	2014	5/1/2014	7,884	1,274
6	2014	6/1/2014	6,592	2,462
7	2014	7/1/2014	6,994	2,798
8	2014	8/1/2014	8,177	3,217
9	2014	9/1/2014	8,255	2,617
10	2014	10/1/2014	12,500	2,296
11	2014	11/1/2014	12,031	1,933
12	2014	12/1/2014	12,067	1,961
1	2015	1/1/2015	5,976	2,589
2	2015	2/1/2015	6,835	3,575
3	2015	3/1/2015	4,211	1,674
4	2015	4/1/2015	4,498	1,889
5	2015	5/1/2015	6,140	1,679
6	2015	6/1/2015	7,695	2,185
7	2015	7/1/2015	6,319	2,172
8	2015	8/1/2015	6,581	3,204
9	2015	9/1/2015	7,505	2,696
10	2015	10/1/2015	7,614	2,706
11	2015	11/1/2015	9,484	2,367
12	2015	12/1/2015	10,780	2,215
1	2016	1/1/2016	2,740	714
2	2016	2/1/2016	3,174	992
3	2016	3/1/2016	2,883	707
4	2016	4/1/2016	2,469	717
5	2016	5/1/2016	2,533	541
6	2016	6/1/2016	3,455	717
7	2016	7/1/2016	3,279	1,158
8	2016	8/1/2016	3,346	658
9	2016	9/1/2016	3,972	845
10	2016	10/1/2016	3,884	883
11	2016	11/1/2016	3,720	814
12	2016	12/1/2016	3,642	712
1	2017	1/1/2017	2,984	766
2	2017	2/1/2017	3,124	952
3	2017	3/1/2017	2,951	647
4	2017	4/1/2017	3,106	615
5	2017	5/1/2017	3,403	746
6	2017	6/1/2017	3,540	815
7	2017	7/1/2017	1,539	696
8	2017	8/1/2017	1,566	648
9	2017	9/1/2017	2,081	637
10	2017	10/1/2017	3,202	1,029
11	2017	11/1/2017	2,778	923
12	2017	12/1/2017	2,950	929
1	2018	1/1/2018	2,052	613
2	2018	2/1/2018	2,677	1,030
3	2018	3/1/2018	2,652	642
4	2018	4/1/2018	2,024	645
5	2018	5/1/2018	2,790	745
6	2018	6/1/2018	2,249	940

[MFelix · Accepted solution]

Hi @Anonymous ,

Create the following 4 measures:

# Touched current year =
CALCULATE (
    SUM ( 'Table'[Touched] );
    FILTER ( ALL ( 'Table'[Year] ); 'Table'[Year] = MAX ( 'Table'[Year] ) )
)

# Touched PY =
CALCULATE (
    SUM ( 'Table'[Touched] );
    FILTER ( ALL ( 'Table'[Year] ); 'Table'[Year] = MAX ( 'Table'[Year] ) - 1 )
)


% Eligible =
CALCULATE (
    SUM ( 'Table'[Touched] );
    FILTER ( ALL ( 'Table'[Year] ); 'Table'[Year] = MAX ( 'Table'[Year] ) )
)
    / CALCULATE (
        SUM ( 'Table'[Orig_Qualified] );
        FILTER ( ALL ( 'Table'[Year] ); 'Table'[Year] = MAX ( 'Table'[Year] ) )
    )

% Eligible PY =
CALCULATE (
    SUM ( 'Table'[Touched] );
    FILTER ( ALL ( 'Table'[Year] ); 'Table'[Year] = MAX ( 'Table'[Year] ) - 1 )
)
    / CALCULATE (
        SUM ( 'Table'[Orig_Qualified] );
        FILTER ( ALL ( 'Table'[Year] ); 'Table'[Year] = MAX ( 'Table'[Year] ) - 1 )
    )

Now place the first two measures on columns and the last two measures on line.

See attach on PBIX file attach.

Regards,

MFelix

[Anonymous]

Hi @MFelix , Thank you so much. You hit the right chord in 1st attempt.

Regards,

Sushant