Adding Ranks?

[mbmcdonald2]

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!

[tamerj1 · Accepted solution]

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

[mbmcdonald2]

This worked! Thank you so much!!

[SachinNandanwar]

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.

[v-denglli-msft]

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

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.

[mbmcdonald2]

Hi @v-denglli-msft 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!

[SachinNandanwar]

The file is inaccessible.

[mbmcdonald2]

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

[mbmcdonald2]

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!

[SachinNandanwar]

Can you share the PBI file ?

[johnt75]

What are the definitions of the other measures ?

[mbmcdonald2]

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

    )

[bhanu_gautam]

@mbmcdonald2 , 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.

[mbmcdonald2]

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

    )