Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Editing interactions between slicers and charts loses the ranking.
Can somebody point me in the right direction, please?
Solved! Go to 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.
@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
)
)
@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.
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.
Hi @MelStaunton,
Congratulations on finding a solution!
If the issue has been solved, please adopt your final solution to help others.
Best Regards,
Link
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
87 | |
84 | |
65 | |
49 |
User | Count |
---|---|
140 | |
114 | |
110 | |
59 | |
59 |