Top 10 and RankX Issue

DAX DAX Calculations
1

Hi,

I created a data model, tables: Property, Tenant, Charge codes: are created using Addcolumns(Value formula.

image
image1001Ă—630 16 KB

While running Rankx formula I noticed slightly error when I filter the dates few amounts are not ranked correctly, 3 is double-counted and 52 is missing. Is there any way to avoid this?

Ranking = RANKX(ALL(Tenant),[Total Revenue],DESC)

image
image

Top 10 Customers:

Top 10 Tenants = CALCULATE([Total Revenue],
TOPN(10, ALL(Tenant),[Total Revenue],DESC),
VALUE(Tenant[Tenant Name]))

The formula pattern is exactly the same as @sam.mckay uses in Mastering Dax Calculations the only difference is that my Tenants table is build using Addcolumns Value function.

Unfortunately, the results do not display:

How could I resolve the issue?

image

I will appreciate your help.

2

Replace VALUE with VALUES and in Ranking try using single column inside ALL.

3 Likes
3

Thank you @AntrikshSharma ,

What is the difference betwee Value and Values?

4

Hello @Matty,

The difference between a Value and Values is Value converts your text number into a Numeric data type i.e typed string, “3”, into the numeric value 3 whereas Values is a table function which will provide you with the list of values in a column. for eg. use New Table option and type VALUES( Products[Product Name] ) and you’ll receive a list of all the product names in a column.

Hoping you find this useful and helps you in understanding the concept in a better way. :slightly_smiling_face:

Thanks & Warm Regards,
Harsh

1 Like
5

Thank @Harsh makes all sense.

I am starting to use more often Power Bi now. I haven;t used it for over a year :frowning: .

THank you for all your help!

6

Hello @Matty,

You’re welcome. :slightly_smiling_face:

Glad we’re able to help you.

Just keep practicing on day-to-day basis and you’ll start gaining confidence to solve more complex problems. :+1:

Thanks & Warm Regards,
Harsh

1 Like
7

I will @Harsh

Once again thank you :slight_smile:

8

Also in addition to what Harsh said, VALUE can also be used to convert date time in string to decimal number where integer part is the day since 12/31/1899 and the decimal part is time, example:

Measure 2 = VALUE("1/1/2020 12pm")

value
image

Fun fact you can achieve the same result in first picture with just a or p instead of am or pm