topic Re: Adding Ranks? in DAX Commands and Tips

Adding Ranks?

Anonymous — Wed, 06 Nov 2024 14:09:02 GMT

Hi All,

I'm wondering if someone could help me with the following: I have two separate ranks I've done for 2 separate measures and I would like to be able to add up these ranking numbers to create sort of a "score" to combine these two measures. As you can see in the screenshot below, each zone is receiving a score of 2 (where East should have a score of 4, Central a score of 3, and West a score of 5).

Do you know if it is possible to achieve this?

Here is my current calculation:

Consumer Preference Score = [Industry Per Caps Rank]+[Observed Drinker Rank]

Thank you in advance!

Re: Adding Ranks?

bhanu_gautam — Wed, 06 Nov 2024 14:36:07 GMT

Anonymous , Try using

Ensure that you have calculated the ranks for both measures correctly. You can use the RANKX function in Power BI to rank your measures.

Industry Per Caps Rank = RANKX(ALL('Table'), 'Table'[Industry Per Caps], , ASC, Dense)
Observed Drinker Rank = RANKX(ALL('Table'), 'Table'[Observed Drinker], , ASC, Dense)

Create the Combined Score: Once you have the ranks, you can create a new measure to sum these ranks.
Consumer Preference Score = [Industry Per Caps Rank] + [Observed Drinker Rank]

Visualize the Combined Score: Add the Consumer Preference Score measure to your visual to see the combined score for each zone.

Re: Adding Ranks?

johnt75 — Wed, 06 Nov 2024 14:37:31 GMT

What are the definitions of the other measures ?

Re: Adding Ranks?

Anonymous — Wed, 06 Nov 2024 15:04:26 GMT

Hi there, thank you so much for your response. So it's a bit tricky because these ranks are built with parameters. See below.

Industry Per Caps Rank =

VAR OverallRank =

RANKX(

ALLSELECTED ('Industry per Caps Rank'),

CALCULATE (SUM ('Industry per Caps Rank'[Industry Per Caps])),

DESC,Dense)

VAR SubCategoryRank =

RANKX (

ALLSELECTED ('Industry per Caps Rank'[Sub-Category]),

CALCULATE (sum('Industry per Caps Rank'[Industry Per Caps])),

DESC,

Dense

)

VAR ZoneRank =

RANKX (

ALLSELECTED ('Industry per Caps Rank'[Zone]),

CALCULATE (SUM ('Industry per Caps Rank'[Industry Per Caps])),

DESC,

Dense

)

VAR ChannelRank =

RANKX (

ALLSELECTED ('Industry per Caps Rank'[Channel]),

CALCULATE (SUM('Industry per Caps Rank'[Industry Per Caps])),

DESC,

Dense

)

VAR ManufacturerRank =

RANKX (

ALLSELECTED ('Industry per Caps Rank'[Manufacturer]),

CALCULATE(SUM ('Industry per Caps Rank'[Industry Per Caps])),

DESC,

DENSE

)

VAR CategoryRank =

RANKX (

ALLSELECTED ('Industry per Caps Rank'[Category]),

CALCULATE(SUM ('Industry per Caps Rank'[Industry Per Caps])),

DESC,

DENSE

)

RETURN

SWITCH (

TRUE (),

CONTAINSSTRING ( SELECTEDVALUE ('Selection Parameter'[Type]), "Channel" ) , ChannelRank,

CONTAINSSTRING ( SELECTEDVALUE ('Selection Parameter'[Type]), "Zone" ), ZoneRank,

CONTAINSSTRING (SELECTEDVALUE ( 'Selection Parameter'[Type]), "Sub"), SubCategoryRank,

CONTAINSSTRING (SELECTEDVALUE ( 'Selection Parameter'[Type]), "Category"), CategoryRank,

CONTAINSSTRING (SELECTEDVALUE ('Selection Parameter'[Type]), "Manufacturer"), ManufacturerRank,

OverallRank

)

Re: Adding Ranks?

Anonymous — Thu, 07 Nov 2024 14:46:52 GMT

Hi there, thank you so much for your response. So it's a bit tricky because these ranks are built with parameters. See below.

Industry Per Caps Rank =

VAR OverallRank =

RANKX(

ALLSELECTED ('Industry per Caps Rank'),

CALCULATE (SUM ('Industry per Caps Rank'[Industry Per Caps])),

DESC,Dense)

VAR SubCategoryRank =

RANKX (

ALLSELECTED ('Industry per Caps Rank'[Sub-Category]),

CALCULATE (sum('Industry per Caps Rank'[Industry Per Caps])),

DESC,

Dense

)

VAR ZoneRank =

RANKX (

ALLSELECTED ('Industry per Caps Rank'[Zone]),

CALCULATE (SUM ('Industry per Caps Rank'[Industry Per Caps])),

DESC,

Dense

)

VAR ChannelRank =

RANKX (

ALLSELECTED ('Industry per Caps Rank'[Channel]),

CALCULATE (SUM('Industry per Caps Rank'[Industry Per Caps])),

DESC,

Dense

)

VAR ManufacturerRank =

RANKX (

ALLSELECTED ('Industry per Caps Rank'[Manufacturer]),

CALCULATE(SUM ('Industry per Caps Rank'[Industry Per Caps])),

DESC,

DENSE

)

VAR CategoryRank =

RANKX (

ALLSELECTED ('Industry per Caps Rank'[Category]),

CALCULATE(SUM ('Industry per Caps Rank'[Industry Per Caps])),

DESC,

DENSE

)

RETURN

SWITCH (

TRUE (),

CONTAINSSTRING ( SELECTEDVALUE ('Selection Parameter'[Type]), "Channel" ) , ChannelRank,

CONTAINSSTRING ( SELECTEDVALUE ('Selection Parameter'[Type]), "Zone" ), ZoneRank,

CONTAINSSTRING (SELECTEDVALUE ( 'Selection Parameter'[Type]), "Sub"), SubCategoryRank,

CONTAINSSTRING (SELECTEDVALUE ( 'Selection Parameter'[Type]), "Category"), CategoryRank,

CONTAINSSTRING (SELECTEDVALUE ('Selection Parameter'[Type]), "Manufacturer"), ManufacturerRank,

OverallRank

)

Re: Adding Ranks?

Anonymous — Thu, 07 Nov 2024 20:01:52 GMT

Hello SachinNandanwar I created a new forum for my follow up question to this inquiry - do you think you would be able to help me here? Essentially I would like to add two ranks together, both of which are calculated using parameters. Any assistance would be greatly appreciated.

Thank you very much in advance!

Re: Adding Ranks?

SachinNandanwar — Fri, 08 Nov 2024 04:40:27 GMT

Can you share the PBI file ?

Re: Adding Ranks?

Anonymous — Fri, 08 Nov 2024 05:23:26 GMT

Thanks for the reply from bhanu_gautam, johnt75 and SachinNandanwar, please allow me to provide another insight.
Hi Anonymous ,

I am not sure how your dataset is designed, below is my sample.

The Industry per Caps Rank table.

The Selection Parameter table.

Add the two ranks together using the following measure.

VAR _tb1 =SUMMARIZECOLUMNS('Industry per Caps Rank'[Zone],"A",[Observed Drinkers Rank]) VAR _tb2 =SUMMARIZECOLUMNS('Industry per Caps Rank'[Zone],"B",[Industry Per Caps Rank]) VAR _tb3 = ADDCOLUMNS( _tb1,"C",[A]+SUMX(FILTER(_tb2,[Zone] = EARLIER('Industry per Caps Rank'[Zone])),[B]) ) RETURN SUMX(_tb3,[C])

The final result is as follows, hopefully it will meet your needs.

If the above solutions do not solve the problem, please provide a sample PBIX file for us to understand the problem better and help you.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

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

Re: Adding Ranks?

Anonymous — Fri, 08 Nov 2024 14:27:46 GMT

Hi Anonymous and SachinNandanwar thank you so much for all of the help. Please find my sample upload here for further context. Any assistance is greatly appreciated!

Re: Adding Ranks?

SachinNandanwar — Fri, 08 Nov 2024 16:15:14 GMT

The file is inaccessible.

Re: Adding Ranks?

Anonymous — Fri, 08 Nov 2024 16:20:20 GMT

Hi Sachin! So sorry about that. Please try now!

Re: Adding Ranks?

SachinNandanwar — Sun, 10 Nov 2024 14:18:43 GMT

Only way I can see this can solved is by combing both the two tables , with the columns "observered drink" and "Industry Per caps" and then setting up field parameters on this new table.

Re: Adding Ranks?

tamerj1 — Mon, 11 Nov 2024 07:03:43 GMT

Hi Anonymous
Allways use a common dimention. Attached sample file with the proposed solution.

Re: Adding Ranks?

Anonymous — Mon, 11 Nov 2024 20:51:37 GMT

This worked! Thank you so much!!