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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.