cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ankita_92
Frequent Visitor

Top N Locations based on 2 measures

I need Top N Locations based on their sum of scores and ordered by the sum of Total Loss amount, and want to the locations to be ranked for the records where their sum of Toptal Loss amount is not blank, I am able to get the ranked locations based on sum of Total Loss amount but not able to get their sum of scores(of Ranked locations), I am getting al the locations, please help.

Measure =
var RankingDimension = values('Table'[Location name])
Var
RankingSelect = selectedvalue('Top N RG'[Top N RG])
var ranking2 = if(isblank(calculate(sum('Table'[Total Loss]))), blank(),
rankx(filter(All('Table'[Location name]), not(isblank(calculate(sum('Table'[Total Loss]))))),
calculate(sum('Table'[Total Loss])),, desc,skip))
return
ranking2
Please help!

I am not allowed to share the pbix file or any attachment but let me share the expectations, suppose I have 10 locations- L1,L2.........L10 with their respective scores aand Total loss amounts, few loss amounts are null, I want top 2 locations based on their scores and also ordered by their Total loss amounts but consider onlly non blank values of Total loss amounts

Location      Score       Total LossAmount

L1                500            10000

L2                450            9000

 tried below dax Calculations also

///////////1

CALCULATE(sum('Table'[score]), Filter(RankingDimension,ranking2 <= RankingSelect ))
 ////////2
calculate(sum('Table'[Score]),TOPN(5,VALUES('Table'[Location Name]),calculate(sum('Table'[Score])),desc)))
/////////3
calculate(
sum('Table'[Score]),
filter(
ADDCOLUMNS(RankingDimension,"Scores",calculate(sum('Table'[Score]))),
countrows(
filter(
CALCULATETABLE(RankingDimension,filter(RankingDimension,ranking2<=5)), ranking2 <=5)) >0))
////////////////// 4
calculate(
sum('table'[Score]),
filter(RankingDimension,
countrows(
filter(
CALCULATETABLE(RankingDimension,filter(RankingDimension,ranking2<=5)), ranking2 <=5)) >0))

 

2 ACCEPTED SOLUTIONS

Hi @ankita_92 

Just offering my take on it.

From your description, you want to rank Locations based on Score, but limited to Locations where sum of Total Loss Amount is nonblank - hopefully I interpreted that correctly 🙂

 

If Location will be included as a field in the visual, you can create a 0/1 measure for the purpose of filtering.

 

I have attached a test PBIX, with a Location Filter measure:

Location Filter = 
IF (
    NOT ISBLANK ( SUM ( 'Table'[Total Loss Amount] ) ),
    VAR LocationsForRanking = 
        FILTER (
            ALLSELECTED ( 'Table'[Location] ), -- You may want to use ALL rather than ALLSELECTED
            NOT ISBLANK ( CALCULATE ( SUM ( 'Table'[Total Loss Amount] ) ) )
        )
    VAR ScoreRank =
        RANKX ( 
            LocationsForRanking,
            CALCULATE ( SUM ( 'Table'[Score] ) )
        )
    RETURN
        INT ( ScoreRank >= 1 && ScoreRank <= [Top N RG Value] )
)

A visual-level filter is then applied to any visuals, with this measure set equal to 1.

The visuals themselves can then be sorted by Score.

 

This method works regardless of which measures are shown in the visual. 

 

Is this close to what you were wanting to do?

 

Regards,

Owen


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

Thanks a lot @OwenAuger for your help, it is exactly what I wanted, However I have implemented the below code its working for me, please see which is better (performance wise also as using Direct Query)

var ranking2 = if(isblank(calculate(sum('Table'[Total Loss]))), blank(),
rankx(filter(All('Table'[Location name]), not(isblank(calculate(sum('Table'[Total Loss]))))),
calculate(sum('Table'[Total Loss])),, desc,skip))
VAR _tab2 =
CALCULATETABLE (
VALUES ( 'Table'[Location Name] ),
FILTER ( 'Table', not(isblank(sum('Table'[Total Loss]))) )
)

Return

SUMX (
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Location Name]
IN _tab1 && ranking1 <= RankingSelect
)
,
sum(Score)
)

View solution in original post

6 REPLIES 6
selimovd
Community Champion
Community Champion

Hey @ankita_92 ,

 

our friends from SQLBI made an article about exactly that case. Take a look:

RANKX on multiple columns with DAX and Power BI - SQLBI

They also explain it in a video:
Compute a ranking based on multiple columns - YouTube

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Thanks for responding, I have implemented the same, I am getting the rank correctly based on this logic, but need help to return the value of scores for top N locations in a bar chart rather than just the ranking.

I need first say Top 5 locations based on score and their loss amount and then want to display the scores of those top 5 locations in bar chart, trying the below dax, kindly correct.

////////1 

ASISBasedon2measues =
if(HASONEVALUE('Table'[Total Loss]) && [Toop Loss Amount] > 0 && sum('Table'[Score]) > 0 ,
var maxTotalloss = CALCULATE(max('Table'[Total Loss]), removefilters())
var lookuptable =
addcolumns(allselected('Table'[Location Name]), "ASISScore",[Top Score]* maxTotalloss + [Top Loss Amount])
var currentvalue = [Top Score] * maxTotalloss + [Top Loss Amount]
var result = rankx(lookuptable,[AsIsScore],currentvalue)
return
calculate(
sum('Table'[Score]),
filter(all('Table'[Location Name]),
countrows(
filter(ALL('Table'[Location Name]), result <=5
)) >0)))


where [Top Score] = sum('Table'[Score])
and [Top Loss Amount] = sum('Table'[Total Loss])

amitchandak
Super User IV
Super User IV

@ankita_92 , have tried TOPN,

 

Measures like

 

loss m= calculate(sum('Table'[Total Loss]))

 

CALCULATE([loss m],TOPN(10,allselected('Table'[Location name),[loss m],DESC),VALUES('Table'[Location name))

or

CALCULATE([loss m],TOPN(10,all('Table'[Location name),[loss m],DESC),VALUES('Table'[Location name))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Thanks for responding , but I need Top N locations based on both score as well as total loss amount

Hi @ankita_92 

Just offering my take on it.

From your description, you want to rank Locations based on Score, but limited to Locations where sum of Total Loss Amount is nonblank - hopefully I interpreted that correctly 🙂

 

If Location will be included as a field in the visual, you can create a 0/1 measure for the purpose of filtering.

 

I have attached a test PBIX, with a Location Filter measure:

Location Filter = 
IF (
    NOT ISBLANK ( SUM ( 'Table'[Total Loss Amount] ) ),
    VAR LocationsForRanking = 
        FILTER (
            ALLSELECTED ( 'Table'[Location] ), -- You may want to use ALL rather than ALLSELECTED
            NOT ISBLANK ( CALCULATE ( SUM ( 'Table'[Total Loss Amount] ) ) )
        )
    VAR ScoreRank =
        RANKX ( 
            LocationsForRanking,
            CALCULATE ( SUM ( 'Table'[Score] ) )
        )
    RETURN
        INT ( ScoreRank >= 1 && ScoreRank <= [Top N RG Value] )
)

A visual-level filter is then applied to any visuals, with this measure set equal to 1.

The visuals themselves can then be sorted by Score.

 

This method works regardless of which measures are shown in the visual. 

 

Is this close to what you were wanting to do?

 

Regards,

Owen


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

Thanks a lot @OwenAuger for your help, it is exactly what I wanted, However I have implemented the below code its working for me, please see which is better (performance wise also as using Direct Query)

var ranking2 = if(isblank(calculate(sum('Table'[Total Loss]))), blank(),
rankx(filter(All('Table'[Location name]), not(isblank(calculate(sum('Table'[Total Loss]))))),
calculate(sum('Table'[Total Loss])),, desc,skip))
VAR _tab2 =
CALCULATETABLE (
VALUES ( 'Table'[Location Name] ),
FILTER ( 'Table', not(isblank(sum('Table'[Total Loss]))) )
)

Return

SUMX (
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Location Name]
IN _tab1 && ranking1 <= RankingSelect
)
,
sum(Score)
)

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.