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:LEProject HeaderCurrencyPlanned sales volumeMonthEndDateCurrent Project StatusProjectTypeCorrectedRankB2756EUR1053427528-Feb-21BacklogM&E New1B2037EUR950000028-Feb-21BacklogM&E New2B2749EUR926424128-Feb-21BacklogM&E New3B2946EUR858387528-Feb-21BacklogM&E New4B2316EUR731546628-Feb-21BacklogM&E New5C3120001EUR555515428-Feb-21BacklogM&E BTP6B3121EUR385000028-Feb-21BacklogM&E BTP7B2754EUR377929728-Feb-21BacklogM&E New8B3120EUR370000028-Feb-21BacklogM&E BTP9C3119222EUR304432228-Feb-21BacklogM&E New10C3120204EUR259879528-Feb-21BacklogM&E New C3120231EUR235149728-Feb-21BacklogM&E BTP C3119262EUR232907928-Feb-21BacklogM&E New KRLD20076EUR213051028-Feb-21BacklogM&E New C3120166EUR198360528-Feb-21BacklogM&E New C3119235EUR175990628-Feb-21BacklogM&E New C3120238EUR163149228-Feb-21BacklogM&E BTP C3120048EUR95882328-Feb-21BacklogM&E BTP KRLD20121EUR87224928-Feb-21BacklogM&E New C3120076EUR45501528-Feb-21BacklogM&E New KRLD20132EUR25021828-Feb-21BacklogM&E New KRLD20143EUR6353328-Feb-21BacklogM&E New KRLD20111EUR3216928-Feb-21BacklogM&E New KRLD20137EUR3207028-Feb-21BacklogM&E New KRLD20154EUR1275828-Feb-21BacklogM&E New KRLD20088EUR280728-Feb-21BacklogM&E New C3120001EUR554828831-Jan-21BacklogM&E BTP B3121EUR385000031-Jan-21BacklogM&E BTP B3120EUR370000031-Jan-21BacklogM&E BTP C3119222EUR291462831-Jan-21BacklogM&E New B2035EUR280825831-Jan-21BacklogM&E New C3120204EUR259558331-Jan-21BacklogM&E New C3120231EUR234859131-Jan-21BacklogM&E BTP C3119262EUR232620131-Jan-21BacklogM&E New KRLD20076EUR213973631-Jan-21BacklogM&E New C3120166EUR198115431-Jan-21BacklogM&E New C3119235EUR175773131-Jan-21BacklogM&E New C3120238EUR162947631-Jan-21BacklogM&E BTP C3120048EUR95763831-Jan-21BacklogM&E BTP KRLD20121EUR87602631-Jan-21Order EntryM&E New B2323EUR61000031-Jan-21BacklogM&E BTP C3120076EUR45445331-Jan-21BacklogM&E New KRLD20122EUR38438431-Jan-21BacklogM&E New KRLD-2013EUR25130131-Jan-21Order EntryM&E New KRLD20143EUR6380831-Jan-21BacklogM&E New KRLD20111EUR3230831-Jan-21BacklogM&E New KRLD20137EUR3220931-Jan-21BacklogM&E New KRLD20154EUR1281331-Jan-21BacklogM&E New KRLD20088EUR281931-Jan-21BacklogM&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 chartEditing interactions between slicers and charts loses the ranking.Can somebody point me in the right direction, please?

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 tableTop10Sales=SUMMARIZE('Table', 'Table'[Project Header], 'Table'[Project Currency], 'Table'[Year], 'Table'[MonthEndDate]that populates the Sparklines after relationships were established betweenTable & ListHeadersListHeaders & Top10 SalesDim 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.

MelStaunton

Helper III

5 years ago

Solved

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

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?

Need Help

MelStaunton
5 years ago
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.

4 Replies

amitchandak
Super User
5 years ago
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
  Helper III
  5 years ago
  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
    Helper III
    5 years ago
    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.

Forum Discussion

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

4 Replies

Related content

Dynamic images based on selections

Dynamic Filtering based on date selection

Select the Top 5 projects

Dynamic Cards by Selection on a Chart

Dynamic Project Tracker