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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jajoerger
Frequent Visitor

Matrix to display TopN based on grand total and not monthly values

I have the following measure that is displaying more than TOPN.  I want top 15 clients.  I believe it's because I have YEAR and MONTH in my results.  Ideally I'd like the top N to be based on the 2023 total and if that isn't possible then based on the grand total for each line of data.

Top N Hours =
VAR TopNValue = 'Top N Clients'[Top N Clients Value]

Var TopClientHours =
TOPN(
    TopNValue,
    ALLSELECTED(TopHours),
    EmpTime_Clients[HoursPYTD]
    )
Var AllHours =
CALCULATE(
    EmpTime_Clients[HoursPYTD],
    ALLSELECTED(TopHours)
)
Var OtherHours = AllHours - CALCULATE(EmpTime_Clients[TotalHrs], TopClientHours)
VAR TopTHours =
CALCULATE(
    EmpTime_Clients[TotalHrs],
    KEEPFILTERS(
        TopClientHours
    )
)
Var currentTHours = SELECTEDVALUE(TopHours[Client])
RETURN
IF(
    currentTHours= "* Other",
    OtherHours,
    TopTHours
)
 
jajoerger_0-1707834307756.png

 

 I want my visual to show 15 clients and *Other.
2 REPLIES 2
amitchandak
Super User
Super User

@jajoerger , Create a measure like

Last Year = calculate(Sum(EmpTime_Clients[TotalHrs]) , previousyear(Date[Date])

 

Use this as visual level filter for TOPN filter

 

Or create another measure like

 

calculate(Sum(EmpTime_Clients[TotalHrs]) ,keepfilters(topn(10, allselected(EmpTime_Clients[Client]), [last Year], desc) ))

 

Power BI Tutorial Series for Beginners Part 42 TOPN: https://youtu.be/QIVEFp-QiOk
TOPN with Numeric Parameter -https://youtu.be/cN8AO3_vmlY?t=26448

 

Power BI- TOPN with Others- https://youtu.be/I_TY4hVlzAE
https://medium.com/microsoft-power-bi/power-bi-topn-others-8b094203a306

I'm not quite following 100%.  Should I delete the measure (Top N Hours) that I created and create a new one?

jajoerger_0-1707849270242.png

 

Helpful resources

Announcements
PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors