Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- DAX Commands and Tips
- Top N Locations based on 2 measures

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Top N Locations based on 2 measures

06-14-2021
09:22 AM

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

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-16-2021
05:23 AM

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

My Blog

Connect on Twitter

Connect on LinkedIn

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-16-2021
08:16 AM

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)

)

6 REPLIES 6

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-14-2021
12:02 PM

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

Blog: __WhatTheFact.bi__

Follow me: __twitter.com/DenSelimovic__

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-14-2021
12:13 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-14-2021
10:33 AM

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

Proud to be a Super User!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-14-2021
10:36 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-16-2021
05:23 AM

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

My Blog

Connect on Twitter

Connect on LinkedIn

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-16-2021
08:16 AM

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)

)

Top Solution Authors

User | Count |
---|---|

43 | |

20 | |

20 | |

19 | |

15 |

Top Kudoed Authors

User | Count |
---|---|

29 | |

18 | |

14 | |

12 | |

12 |