Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
MelStaunton
Helper III
Helper III

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:

LEProject HeaderCurrencyPlanned sales volumeMonthEndDateCurrent Project StatusProjectTypeCorrectedRank
B2756EUR1053427528-Feb-21BacklogM&E New1
B2037EUR950000028-Feb-21BacklogM&E New2
B2749EUR926424128-Feb-21BacklogM&E New3
B2946EUR858387528-Feb-21BacklogM&E New4
B2316EUR731546628-Feb-21BacklogM&E New5
C3120001EUR555515428-Feb-21BacklogM&E BTP6
B3121EUR385000028-Feb-21BacklogM&E BTP7
B2754EUR377929728-Feb-21BacklogM&E New8
B3120EUR370000028-Feb-21BacklogM&E BTP9
C3119222EUR304432228-Feb-21BacklogM&E New10
C3120204EUR259879528-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 chartEdit 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?

1 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.

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@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
)
)

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.