cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hfoth
Resolver I
Resolver I

RANKX Measure

Hi to everyone!

 

I'm stuck with a RANKX measure that ranks always in 1 position. Hope somebody can help me with this.

 

Sin título.png

As you can see, I have a matrix opened by "Grupo". I need to rank each of them by sales. The sales measure is already calculated and used in the RANKX measure.

 

What am I doing wrong?

 

Thanks in advance.

1 ACCEPTED SOLUTION

Hi everyone!

 

Happy to say that is know working, and ashamed after discovering the problem.

 

The "Grupo" shown in the matrix, is from the facts table, not from the table "GruposIMS". I just changed the dimension of the matrix and the RANKX measure started to show real values.

 

Thanks for the help.

View solution in original post

8 REPLIES 8
ccakjcrx
Resolver I
Resolver I

@hfoth

 

Hey!

 

I have to make presumptions concerning measures used in your IF/RANKX expression. When testing I used a table with salaries, and then I created a measure MsrSumSalary where I summed salaries:

 

MsrSumSalary = 
SUM(Salary[Salary])

I then created a RANKX expression , similar to yours: 

 

Rank Salary = 
IF(
    [MsrSumSalary] <> 0,
    RANKX(
         ALLSELECTED(Salary),ROUND(Salary[MsrSumSalary],0)
    )
)

Here is a screenshot of my table:

Rankx1.jpeg

 

HERE is a link to my .pbix file.

 

The biggest difference is with ALLSELECTED; you appeared to reference a column. When I changed to reference the table, all seemed okay.

 

Hi @ccakjcrx. Thanks for your reply.

 

I've tried as you described but it's not working for me. The IF condition could be or not, never mind about that. The problem is with the RANKX.

 

Here is my model:

Sin título1.png

 

This is the Sales measure:

Ventas IMS = 
SWITCH(
	VALUES('Selector Medida'[Medida]);
	"USD";
	'Medidas VentasIMS'[Ventas IMS MUSD];
	"ML";
	'Medidas VentasIMS'[Ventas IMS ML];
	"UN";
	'Medidas VentasIMS'[Ventas IMS MUN]
)

And "USD" as example:

Ventas IMS MUSD = 
SWITCH(
	VALUES(
		'Selector Período'[Período]
	);
	"YTD";
    CALCULATE(
        SUM(
            'Ventas IMS'[ImporteVendidoUSD]
        );
        DATESYTD(
            'Período IMS'[Fecha]
        )
    );
	"MTH";
    SUM(
        'Ventas IMS'[ImporteVendidoUSD]
    );
	"MAT";
    CALCULATE(
        SUM(
            'Ventas IMS'[ImporteVendidoUSD]
        );
        DATESINPERIOD(
            'Período IMS'[Fecha];
            LASTDATE(
                'Período IMS'[Fecha]
            );
            -1;
            YEAR
        )
    )
)

And the RANKX:

Ranking Grupos = 
RANKX(
    ALLSELECTED('GruposIMS');
    ROUND('Medidas VentasIMS'[Ventas IMS];0)
)

I've deleted the IF condition.

 

I'm still getting 1 position of rank for all options of "Grupo".

 

Any other advice?

Hi @hfoth,

 

There could be something wrong with your model. The table GropusIMS has no relationship with the table Selector Medida. The second parameter of RANKX could always return the same value. So the result of RANKX could be the same. Can you share the pbix file? You can delete all the confidential parts 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 @v-jiascu-msft.

 

I've tried introducing all the calculations that involve "Selector Medida" to the RANKX measure without success,

 

Anyway, the kind of logic that I'm using for this RANKX measure that is not working, is being used in a other measure and it's working fine:

Ranking MegaPharma = 
IF(
	'Medidas VentasIMS'[Ventas IMS] <> 0;
	CALCULATE(
		RANKX(
			ALL('GruposIMS'[Grupo]);
			ROUND('Medidas VentasIMS'[Ventas IMS];0)
		);
		FILTER(
			ALL('GruposIMS'[Grupo]);
			'GruposIMS'[Grupo] = "** MEGA PHARMA **"
		)
	)
)

This measure is used here, in the 2 left tables:

Sin título2.png

Hi everyone!

 

Happy to say that is know working, and ashamed after discovering the problem.

 

The "Grupo" shown in the matrix, is from the facts table, not from the table "GruposIMS". I just changed the dimension of the matrix and the RANKX measure started to show real values.

 

Thanks for the help.

View solution in original post

@hfoth

 

Please try this

 

Ranking Grupos =
RANKX (
    ALLSELECTED ( 'GruposIMS' ),
    CALCULATE ( VALUES ( 'Medidas VentasIMS'[Ventas IMS] ) )
)

 

 

 

 


Regards
Zubair

Please try my custom visuals

Hi @Zubair_Muhammad.

 

VALUES does not work with measures. It only works when calling a column of a table.

@hfoth

 

My aplogies. I thought 

 

'Medidas VentasIMS'[Ventas IMS]

 

is a calculated column


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors