Why do the same values have different rank number?

[Anonymous]

Hi all,

I find a strange thing in my report. I have ID, expenses, and rank.

The ID column is just a column value,

The Expenses column is a sum of amount, not a measure.

RANK = RANKX(ALL(DiDi[employee_id]),[Measure 2],,DESC,Skip)
Measure 2 = SUM([amount_final])

As you can see, these IDs have the same expenses - 2500, but the rank is different.

I have checked the details. The sum of the amount is just 2500, with no decimals.

And I want to check the reason, so I reduce the table, now the table only has month, order_id, id, type, amount columns.

I'm still trying to figure out the reason but don't know how to do.

If you have any suggestions or know the reason, please tell me.

If you need more information, please tell me.

Thanks in advance.

Aiolos Zhao

[Anonymous]

I study with the decimal number and fixed decimal number.

Then I change my amount column to fixed-decimal-number type, it solved.

But I still want to know why the number is different between the MYSQL database and power bi desktop.

Aiolos Zhao

[danextian]

I've re-read your comment and realized that you mentioned double(10,2) which should return just two decimal places.  I've found a similar issue on https://community.powerbi.com/t5/Desktop/Power-Query-decimal-precision-problem-does-not-get-to-0-whe.... 

[Anonymous]

Hi @danextian,

Sorry for reply late and thanks for your reply.

Yes, I'm using the double(10,2) in my MYSQL database for this column, so I think it won't be the MYSQL problem.

And I saw the link you mentions, do you think it is a bug of Power BI about precision decimal?

For now, I can use "Change Type to decimal number" to solve this problem.

Thanks.

Aiolos Zhao

[danextian]

Hi @Anonymous 

It could be that what you see on MYSQL is truncated and Power BI just loads the exact number. 

[V-pazhen-msft]

Hi,  @Anonymous 

I think I would need more information for the solution. If you can provide a data table and drag your “Measure 2” into the visual table, It should be more clear. 

According to the current description, it might be because you ranked with a measure but include a column in the table. Importantly, A measure only considers the current row, and a column considers each row. They return different values even with the same formula. I have an example for you to clarify:

SumMeasure = SUM([Value])
SumColumn = SUM([Value])
Rank = RANKX(ALL(Sheet1[Index]),[SumMeasure],,DESC,Skip)

 

Best, 
Paul

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

[Anonymous]

Hi @V-pazhen-msft ,

Thanks for your reply. I checked my measure. I think it's correct.

I found my problem, and you can see my reply above.

The value is 52.99 in my MYSQL table, but it shows 52.99.....02 in my power bi desktop.

It's strange, and I'm trying to load the id only to see whether it still has the issue.

Aiolos Zhao

[Anonymous]

I have grouped by id, month, and type to sum amount. (Reduce order id)

Now the table only has around 30,000 rows, but the result is also wrong.

Aiolos Zhao

[danextian]

Hi  , 

Have you tried creating  a checker formula to determine if indeed your measure returns a flat 2500 for each ID? You can tryChecker = [Measure] = 2500which will return true/false. It could be that your 2500 is actually 2500.00000000012121. 

Is your ID being sorted by another column? If it is, the sort by column should be included in your RANKX argument. 

Rank =
RANKX ( ALL ( 'Table'[ID], 'Table'[SortBy] ), [Sum of Values],, DESC, SKIP )

@Anonymous

[Anonymous]

Hi @danextian,

Thanks for your reply. I use the checker like you said, it works.

But now I wonder why the number is ......02, in the database, I'm using MySQL, the type of amount column is double(10,2), the value is 52.99.

But after I loaded into power bi, it change to 52.99........02.

Aiolos Zhao