Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Adding Ranks?

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!
 

 

13 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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
          )
  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    What are the definitions of the other measures ?

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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
          )
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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!

  • SachinNandanwar's avatar
    SachinNandanwar
    Impactful Individual
    1 year ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      This worked! Thank you so much!!