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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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!
Blog
Twitter
LinkedIn

View solution in original post

Anonymous
Not applicable

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
Super User
Super User

Hey @Anonymous ,

 

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
 
Anonymous
Not applicable

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
Super User

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

Anonymous
Not applicable

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

Hi @Anonymous 

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!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors