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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.