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
ADP007
Helper IV
Helper IV

rank

Hello,

 

Havong difficulty with a rank formula 

Ranking-1 = RANKX(ALL('Media Agency'[Media agency Group]);CALCULATE([FY-1 Price])) 

 

Result is correct when used in a matrix, when used in a Card with a visual filter on GroupM the result is different.

Live connection on a Tabular cube.

Any help more than welcome as always

 

Thanks

ADP

 

1.jpg

2 ACCEPTED SOLUTIONS

Hi @ADP007,

 

It should be 11. The YYYYMM won't change the rank number as you need. You can't use the old measure as standards. 

rank2

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi @ADP007,

 

Could you please mark the proper answers as solutions?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

11 REPLIES 11
Zubair_Muhammad
Community Champion
Community Champion

@ADP007

 

What if you use ALLSELECTED??

 

Ranking-1 = RANKX(ALLSELECTED('Media Agency'[Media agency Group]);CALCULATE([FY-1 Price])) 

 

 


Regards
Zubair

Please try my custom visuals

Hi,

 

I tried that also. The result is ok in the matrix but then in the card the ranking is 1 and no longer 5. In both cases the ranking in the card is incorrect

 

Thanks 

 

.2.jpg

Hi @ADP007,

 

Can you share the formula of [FY-1 Price]? Share the file please if you can. BTW, how did you apply the visual level filter?

 

Best Regards,

Dale

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Dale,

 

 [FY-1 Price] is a calculated measure in my tabular model 

FY-1 Price:=CALCULATE
 (
    [TotalPrice];
    FILTER(Calendar;Calendar[MDB_FYIndex] =  -1 )
)

I have a DimDate table where some index give running year, last year etc....

 

How can I share the file with you?

Regards

David

Hi all,

 

I have a reproduced the error in a report I can share

When I unselect a date the ranking is wrong in the Card

Any help always welcome

 

Thanks

D.

 

14-08-2018 10-09-39.jpg

Hi @ADP007,

 

You can upload the file to a cloud drive like OneDrive, GoogleDrive then share the download link here. Please mask the sensitive data first.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hi Dale,

 

Many thanks in advance. I hope the link works.

The error occurs when the selected agency doesn't have any investments for a date.

In the example below OMNICOMEDIAGROUP doesn't have any data before 201807 and when I select that date the ranking in the matrix is correct but the ranking in the card is not correct.

 

PowerBi

 

Regards

David

 

 

14-08-2018 12-03-50.jpg

Hi @ADP007,

 

That's because you introduced new contexts. Please try the formula below.

Rank 2 =
IF (
    CALCULATE ( SUM ( Sheet1[Invest] ) ) = BLANK (),
    999,
    RANKX (
        ALL ( Sheet1[Agency] ),
        CALCULATE ( SUM ( Sheet1[Invest] ), ALLEXCEPT ( Sheet1, Sheet1[Agency] ) )
    )
)

rank

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Dale,

 

Thanks for your reply. It does work for the example with OMNICOMMEDIA but then playing around with the slicer it doesn't always work.

16-08-2018 07-43-29.jpg

 

What I am trying to achieve in another report is to point out the ranking of one specific company GROUPM (company I work for) whatever filter is used (CINEMA , INTERNET, OOH,...)

 

Can't share this report as it is with a direct connexion to an olap cube.

 

16-08-2018 07-47-50.jpgThanks

David

Hi @ADP007,

 

It should be 11. The YYYYMM won't change the rank number as you need. You can't use the old measure as standards. 

rank2

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @ADP007,

 

Could you please mark the proper answers as solutions?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.