RANKX not working as expected

DAX DAX Calculations
1

Hi @Shrik,

I read post RANKX is not working, but didn’t answer my Q.

Actually my RANKX calculation works, but just at total level for Top 20% Industries - see snapshot:

image
image917×528 31.7 KB

image
image789×135 7.15 KB

image
image773×133 6.96 KB

Why is it that the Top 20% is not working but the Bottom 80% is?

I tried a different approach and worked, though not following your RANKX instructions, @sam.mckay:

image
image847×439 20.1 KB

image
image1167×34 3.2 KB

image
image864×75 4.86 KB

Could you please help me understand why the second method worked for both 20% and 80% vs first DAX?

It has nothing to do with changing VALUES by ALL in the first code, which I did and got same result.

Thanks a lot

2

Just looking at quickly, but the variable might be what is causing this issue. Variables are declared where they are defined, not where they are used. But that’s just a quick guess. What happens if you get rid of the variable and just use the expression? or define it later? Worth a shot, but if it’s still wrong can you upload the pbix file?

3

Hi @Nick_M, not sure how can I merge these 2 different calcs using the first approach- any hint here?

Moreover, I want to highlith that I am getting this error message when I calculate 20/80 for customers ([DUNS Group Number (Ship-To)), see below:

image

What is this composite thing?

Customers, coming from Ship-To table, are linked to my SPP CS Reallocation table (revenue). Therefore I would have expect to get the results using second approach - see relationships

image

4

@SamSPAIN
I havent seen that error message before? Are you using import, direct query or composite? I only really deal in import, so maybe that’s where that error is coming from…
Any chance you can upload a sample?

5

Hi Nick, it’s IT department in my company who is responsible to build the dataset based upon my specs.

Although not 100% sure, I’d say they use import method to query data from SAP BW onto PBI.

The PBIX is 100MB, and wouldn’t like to share hereby broadly - however, if you provide me a secure location I can certainly uploaded there so that you can check out what might be causing the issue.

As a recap - I tried to use 2nd approach within first code and didn’t work at all - any hint on how can I merge the two?

6

Hi @Nick_M, I’m coming back on this topic which I haven’t been able to sort it our by myself (I mean the composite key error thing)… Is there any private location where I can post the PBIX? I’m really stuck with it and need to move it forward asap.

Much appreciated

7

Yes to me it is just how the variable is calculating your result initial as it gets completed before anything below return.

You second solution here looks good from my perspective. So hopefully you have been able to work with that.

The only things that you need to understand here is the difference in evaluation of variables.

See here for more details from the mastering dax course.

Thanks
Sam

8

@SamSPAIN
Not sure on the composite key error, would have to see the data model.

What I was suggesting was to just copy the formula you have defined in the variable and paste that full function where you currently have the variable. You need that function to calculate as it is being iterated, and where you have it now, it’s not being evaluated in the row context defined by the RANKX, but rather before it gets to that row.

Like to keep everything in public here, any chance you can put together a small sample file that can be uploaded, assuming the above didnt work?

9

@Nick_M, thanks for checking in - no need to upload a sample as it worked.