Forum Discussion

German_Chris's avatar
German_Chris
New Member
4 years ago
Solved

How to count a rank

Hello, I like to count the rank of prices.   I have the following (example) table: article price Account_No Account_Name Group1 Group2 A 9 60000 Hans 525A E...
COUNT
COUNTX
powerpivot
rank
RANKX
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    German_Chris

    STEP 1:
    Create this calculated table:

    Parater Rank Table = SELECTCOLUMNS( {1,2,3,4,5}, "Rank_Value",[Value] )

    STEP 2: Create this measure:
    Rank Count =
    VAR _current_rank_parameter = SELECTEDVALUE('Parater Rank Table'[Rank_Value])
    VAR _result =
    COUNTROWS(
         FILTER(
                ADDCOLUMNS(
                            VALUES(Prices_History[article]),
                            "@Rank", [Preis_Rank]
                ),
                 [@Rank] = _current_rank_parameter )
    )
    RETURN IF(ISINSCOPE(Prices_History[Group1]),_result)


    STEP 3:
    Put the column from the parameter table on the columns of the matrix and the measue in the values (in the rows put what you put in your pic)


    In case it answered your question, please accept the solution to help other members find it. Appreciate Your Kudos.

    Showcase Report – Contoso By SpartaBI


    Visit SpartaBI website   Visit SpartaBI Linkdin   Visit SpartaBI Facebook

    SpartaBI Logo

SpartaBI's avatar
SpartaBI
Icon for Community Champion rankCommunity Champion
4 years ago

German_Chris

STEP 1:
Create this calculated table:

Parater Rank Table = SELECTCOLUMNS( {1,2,3,4,5}, "Rank_Value",[Value] )

STEP 2: Create this measure:
Rank Count =
VAR _current_rank_parameter = SELECTEDVALUE('Parater Rank Table'[Rank_Value])
VAR _result =
COUNTROWS(
     FILTER(
            ADDCOLUMNS(
                        VALUES(Prices_History[article]),
                        "@Rank", [Preis_Rank]
            ),
             [@Rank] = _current_rank_parameter )
)
RETURN IF(ISINSCOPE(Prices_History[Group1]),_result)


STEP 3:
Put the column from the parameter table on the columns of the matrix and the measue in the values (in the rows put what you put in your pic)


In case it answered your question, please accept the solution to help other members find it. Appreciate Your Kudos.

Showcase Report – Contoso By SpartaBI


Visit SpartaBI website   Visit SpartaBI Linkdin   Visit SpartaBI Facebook

SpartaBI Logo

  • German_Chris's avatar
    German_Chris
    New Member
    4 years ago

    SpartaBI 
    many thanks!
    For Excel I had to change something.

    1. I don´t know how to create a calculated Table in PowerPivot, so I create a "normal" Table an imported it to the model.

    2. Function SELECTEDCOLUMNS does not exists in PowerPIvot-Dax, so I replace it with

     

    VAR  current_rank_parameter = IF(HASONEVALUE(Rank_Table[Rank_Value]);VALUES(Rank_Table[Rank_Value]))

     

     

    3. Function ISINSCOPE does not exist in PowerPIvot-Dax, I'ed replace it with HASONEVALUE()

     

    RETURN IF(HASONEVALUE(Prices_History[Group1]); result)​

     

     

     

    Result is as exspacted


    BR German_Chris