Forum Discussion

Naveennegi119's avatar
Naveennegi119
Helper III
8 years ago
Solved

Hide value

Hi All,   It really very complicated to me to resovle.   when I select sub then it show fine top 3 value (situation 1)       But when i select both(sub and sub category), it show the valu...
Need Help
  • Floriankx's avatar
    Floriankx
    8 years ago

    Hello,

     

    I couldn't test 'Dense' last week so I did it today.

     

    My Measure is TopN:=CALCULATE(MAX(Test_Rank[Data]);FILTER(Test_Rank;RANKX(Test_Rank;[Data];;;Dense)=Top_3[MaxN]))

     

    This is my over all:

     

    For me it looks like it is supposed.

Floriankx's avatar
Floriankx
Solution Sage
8 years ago

Hello,

 

Your MaxN is wrong. I ignored your calculated column and tried to build an independent measure only needing the columns category, sub category and Data.

MaxN should relate to an independent table, which helps you to create Max1, Max2, Max3.

If you want to display Situation 1 only if now Sub Category is selected, maybe this helps:

Situation 1: IF(NOT(HASONEVALUE([Sub Category])),[TopN],BLANK())

Situation 2: IF((HASONEVALUE([Sub Category])),[TopN],BLANK())

 

Maybe this helps.

Naveennegi119's avatar
Naveennegi119
Helper III
8 years ago

Hi Floriankx,

 

I'm confuse with MaxN

 

How I get Max1, Max2 & Max3

 

can u please sent a screenshot

 

Regards,

NICK

  • Floriankx's avatar
    Floriankx
    Solution Sage
    8 years ago

    Hello, I do have them as column in my pivot table.

    A is sliced.

     

    • Naveennegi119's avatar
      Naveennegi119
      Helper III
      8 years ago

      Hi Floriankx,

       

      you giving me solution

       

      I want to know how i get solution step by step

       

      Regards,

      NICK

      • Floriankx's avatar
        Floriankx
        Solution Sage
        8 years ago

        Hello I try to walk you through:

         

        I have a table (extract of your big one) Test_Rank

        SubSub CategoryData
        AGH99
        BIJ96
        BIJ95
        ACD94
        AEF90
        BKL89
        AEF87
        AGH95

         

        Then I do have another table Top_3

        Top 3
        1
        2
        3

         

        Tables are not related.

        In Top_3 i have the measure MaxN:=MAX(Top_3[Top 3])

         

        In Test_Rank I have TopN:=CALCULATE(MAX(Test_Rank[Data]);FILTER(Test_Rank;RANKX(Test_Rank;[Data])=Top_3[MaxN]))

        MAX is just for aggregation, Filter should return only one value but it has to be wrapped by aggregation funtion (Min, Max, Sum, etc. I don't recommend Sum in case Filter returns more values, duplicats e.g.)

        Filter is for Filtering. RANKX ranks your Data. In combination only the value is filtered of TopN value is selected depending on MaxN.

         

        If I create PivotTable with this data it returns the result as shown before. I hope things are clearer now.

         

        Best regards.