use top10 projects of dynamically selected month to plot same projects over time

[MelStaunton]

Hello experts!

I rank Top10 projects by highest sale. Users can select the months (and other slicers)
Whatever projects rank highest for that month I would like to plot a sparkline for all values over time.

Here is some sample data:

LE	Project Header	Currency	Planned sales volume	MonthEndDate	Current Project Status	ProjectTypeCorrected	Rank
B	2756	EUR	10534275	28-Feb-21	Backlog	M&E New	1
B	2037	EUR	9500000	28-Feb-21	Backlog	M&E New	2
B	2749	EUR	9264241	28-Feb-21	Backlog	M&E New	3
B	2946	EUR	8583875	28-Feb-21	Backlog	M&E New	4
B	2316	EUR	7315466	28-Feb-21	Backlog	M&E New	5
C	3120001	EUR	5555154	28-Feb-21	Backlog	M&E BTP	6
B	3121	EUR	3850000	28-Feb-21	Backlog	M&E BTP	7
B	2754	EUR	3779297	28-Feb-21	Backlog	M&E New	8
B	3120	EUR	3700000	28-Feb-21	Backlog	M&E BTP	9
C	3119222	EUR	3044322	28-Feb-21	Backlog	M&E New	10
C	3120204	EUR	2598795	28-Feb-21	Backlog	M&E New
C	3120231	EUR	2351497	28-Feb-21	Backlog	M&E BTP
C	3119262	EUR	2329079	28-Feb-21	Backlog	M&E New
K	RLD20076	EUR	2130510	28-Feb-21	Backlog	M&E New
C	3120166	EUR	1983605	28-Feb-21	Backlog	M&E New
C	3119235	EUR	1759906	28-Feb-21	Backlog	M&E New
C	3120238	EUR	1631492	28-Feb-21	Backlog	M&E BTP
C	3120048	EUR	958823	28-Feb-21	Backlog	M&E BTP
K	RLD20121	EUR	872249	28-Feb-21	Backlog	M&E New
C	3120076	EUR	455015	28-Feb-21	Backlog	M&E New
K	RLD20132	EUR	250218	28-Feb-21	Backlog	M&E New
K	RLD20143	EUR	63533	28-Feb-21	Backlog	M&E New
K	RLD20111	EUR	32169	28-Feb-21	Backlog	M&E New
K	RLD20137	EUR	32070	28-Feb-21	Backlog	M&E New
K	RLD20154	EUR	12758	28-Feb-21	Backlog	M&E New
K	RLD20088	EUR	2807	28-Feb-21	Backlog	M&E New
C	3120001	EUR	5548288	31-Jan-21	Backlog	M&E BTP
B	3121	EUR	3850000	31-Jan-21	Backlog	M&E BTP
B	3120	EUR	3700000	31-Jan-21	Backlog	M&E BTP
C	3119222	EUR	2914628	31-Jan-21	Backlog	M&E New
B	2035	EUR	2808258	31-Jan-21	Backlog	M&E New
C	3120204	EUR	2595583	31-Jan-21	Backlog	M&E New
C	3120231	EUR	2348591	31-Jan-21	Backlog	M&E BTP
C	3119262	EUR	2326201	31-Jan-21	Backlog	M&E New
K	RLD20076	EUR	2139736	31-Jan-21	Backlog	M&E New
C	3120166	EUR	1981154	31-Jan-21	Backlog	M&E New
C	3119235	EUR	1757731	31-Jan-21	Backlog	M&E New
C	3120238	EUR	1629476	31-Jan-21	Backlog	M&E BTP
C	3120048	EUR	957638	31-Jan-21	Backlog	M&E BTP
K	RLD20121	EUR	876026	31-Jan-21	Order Entry	M&E New
B	2323	EUR	610000	31-Jan-21	Backlog	M&E BTP
C	3120076	EUR	454453	31-Jan-21	Backlog	M&E New
K	RLD20122	EUR	384384	31-Jan-21	Backlog	M&E New
K	RLD-2013	EUR	251301	31-Jan-21	Order Entry	M&E New
K	RLD20143	EUR	63808	31-Jan-21	Backlog	M&E New
K	RLD20111	EUR	32308	31-Jan-21	Backlog	M&E New
K	RLD20137	EUR	32209	31-Jan-21	Backlog	M&E New
K	RLD20154	EUR	12813	31-Jan-21	Backlog	M&E New
K	RLD20088	EUR	2819	31-Jan-21	Backlog	M&E New

Ranking measure:

Rank Header = 
IF( HASONEVALUE('Table'[Project Header]),
    RANKX(
       ALLSELECTED('Table'),
        CALCULATE(SUM('Table'[Planned Sales Volume])),
        ,
        DESC,
        Dense
    )
)

 I figure I need to grab the Headers for Top10 ranks and pass them in a filter to another measure but can't seem to get it since I would be passing a 1column/10row table (or array). I do want to keep Currency/LE selected by user.

Edit interactions have been deselected for bottom chart
Editing interactions between slicers and charts loses the ranking.

Can somebody point me in the right direction, please?

[amitchandak]

@MelStaunton , Try a Rank like

RANKX(
ALLSELECTED('Table'[Project Header], 'Table'[LE]),
CALCULATE(SUM('Table'[Planned Sales Volume])),
,
DESC,
Dense
)

or

Rank Header =
IF( HASONEVALUE('Table'[Project Header]),
RANKX(
ALLSELECTED('Table'[Project Header], 'Table'[LE]),
CALCULATE(SUM('Table'[Planned Sales Volume])),
,
DESC,
Dense
)
)

[MelStaunton]

@amitchandak  unfortunately that changes nothing. The ranking in the table for each month is correct. The Sparklines are the top 10 projects of all time and don't correspond to the ones in the table for the selected month. 
I also created a separate date table so the slicer date and the axis date in sparklines would be different.

[MelStaunton · Accepted solution]

So I kinda found a solution for my problem, by first creating a bridge table of Headers:

ListHeaders = VALUES('Table'[Project Header]) 

and then a summarize table

Top10Sales=SUMMARIZE('Table', 'Table'[Project Header], 'Table'[Project Currency], 'Table'[Year], 'Table'[MonthEndDate]

that populates the Sparklines after relationships were established between
Table & ListHeaders
ListHeaders & Top10 Sales
Dim table 'Currency' & Top10Sales.

This is neither elegant nor efficient as I am duplicating lots of data, please feel free to enlighten me on filtering temp tables.

[v-xulin-mstf]

Hi @MelStaunton,

Congratulations on finding a solution!

If the issue has been solved, please adopt your final solution to help others.

Best Regards,

Link