Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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.
Best Regards,
Dale
Hi @ADP007,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
What if you use ALLSELECTED??
Ranking-1 = RANKX(ALLSELECTED('Media Agency'[Media agency Group]);CALCULATE([FY-1 Price]))
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
.
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
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.
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
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.
Regards
David
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] ) ) ) )
Best Regards,
Dale
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.
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.
Thanks
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.
Best Regards,
Dale
Hi @ADP007,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |