Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

how to return dynamic result according to selected value

Hi experts, I want to create a table visual that returns the selected top n table 

here the sample data:

week id value
w1 E01 163
w1 E01 145
w1 E02 94
w1 E02 35
w1 E03 123
w1 E04 8
w1 E06 177
w1 E07 38
w2 E01 103
w2 E02 180
w2 E03 31
w2 E04 57
w2 E06 88
w2 E07 179
w2 E08 69
w3 E02 177
w3 E03 176
w3 E04 62
w3 E06 92

 

and the expecte result is rank of id based on amount of each week, and returns a table with week, id, sum of amount and top n of rank, notice that the rank is based on the slicer of the value, the slicer is another table with no relation with the data above:

topn

1

2

3

...

10,

 

for example, when topn slicer is 3, the expected table is: 

week id sum rank
w1 E01 300 2
w1 E02 200 3
w1 E06 700 1
w2 E02 100 1
w2 E06 20 3
w2 E07 50 2
w3      
…      

when topn slicer is 4, then the table is:

week id sum rank
w1 E01 300 2
w1 E02 200 3
w1 E06 700 1
w1 E07 166 4
w2 E02 100 1
w2 E06 20 3
w2 E07 50 2
w2 E01 10 4
w3      
…     
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, BeaBF 
    I am in favor of your method. At the same time I would like to share another solution.

     

    Hi, Anonymous oakfootballclub 

    I am glad to help you. 

     

    According to your description, you want to how to return dynamic result according to selected value?

    If I understand you correctly, then you can refer to my solution. 

     

    1. Create a new table for handsome selection of top n.

     

    2. New table, calculate the value grouped according to week and id. 

     

     

    Table2 = 
ADDCOLUMNS (
    SUMMARIZE (
        Table_,
        Table_[week],
        Table_[id],
        "TotalValue", SUM ( Table_[value] )
    ),
    "TotalValueByWeekAndID", [TotalValue]
)

     

     

     

    3. New Column, according to the value calculated in the first step of the descending order, to get the top three values. 

     

     

    RankWithinWeekAndID = 
RANKX (
    FILTER ( ALL ( 'Table2' ), 'Table2'[week] = EARLIER ( 'Table2'[week] ) ),
    'Table2'[TotalValueByWeekAndID],
    ,
    DESC,
    DENSE
)

     

     

     

    4.New Measure, and then use the Measure to filter the visual. 

     

     

    TopNFrequency = 
VAR CurrentID =
    SELECTEDVALUE ( Table2[id] )
    Var Top_N = SELECTEDVALUE('Top_Number'[TopN])
RETURN
    CALCULATE (
        COUNTROWS (
            FILTER ( Table2, Table2[RankWithinWeekAndID] <= Top_N && Table2[id] = CurrentID )
        )
    )

     

     

     

    5.Change slicer to single selection:

    6.Here is the result: 

     

     

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.


    Best Regards,
    Fen Ling,


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

4 Replies

  • BeaBF's avatar
    BeaBF
    Icon for Super User rankSuper User
    2 years ago

    Anonymous Hi! Here the measure for rank:

    Rank =

    VAR CurrentWeek = SELECTEDVALUE('Table'[week])
    RETURN
        RANKX(
            FILTER(
                ALL('Table'),
                'Table'[week] = CurrentWeek
            ), CALCULATE(SUM('Table'[value])),
            ,
            DESC, Skip
        )

    if it's ok please accept my answer as a solution!
     
    BBF
      • BeaBF's avatar
        BeaBF
        Icon for Super User rankSuper User
        2 years ago

        oakfootballclub Anonymous the correct measure:

        Rank =

        VAR CurrentWeek = SELECTEDVALUE('Table'[week])
        VAR Calc =
            RANKX(
                FILTER(
                    ALL('Table'),
                    'Table'[week] = CurrentWeek
                ), CALCULATE(SUM('Table'[value])),
                ,
                DESC, Skip
            )
        RETURN IF(Calc <= SELECTEDVALUE(TOPN_TABLE[topn]), Calc)

        now if you select from the slicer 4 it returns:

        if you select 3, it returns:

        The SELECTEDVALUE on week is used only to group by Week in the rank calc.

         

        BBF

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, BeaBF 
    I am in favor of your method. At the same time I would like to share another solution.

     

    Hi, Anonymous oakfootballclub 

    I am glad to help you. 

     

    According to your description, you want to how to return dynamic result according to selected value?

    If I understand you correctly, then you can refer to my solution. 

     

    1. Create a new table for handsome selection of top n.

     

    2. New table, calculate the value grouped according to week and id. 

     

     

    Table2 = 
ADDCOLUMNS (
    SUMMARIZE (
        Table_,
        Table_[week],
        Table_[id],
        "TotalValue", SUM ( Table_[value] )
    ),
    "TotalValueByWeekAndID", [TotalValue]
)

     

     

     

    3. New Column, according to the value calculated in the first step of the descending order, to get the top three values. 

     

     

    RankWithinWeekAndID = 
RANKX (
    FILTER ( ALL ( 'Table2' ), 'Table2'[week] = EARLIER ( 'Table2'[week] ) ),
    'Table2'[TotalValueByWeekAndID],
    ,
    DESC,
    DENSE
)

     

     

     

    4.New Measure, and then use the Measure to filter the visual. 

     

     

    TopNFrequency = 
VAR CurrentID =
    SELECTEDVALUE ( Table2[id] )
    Var Top_N = SELECTEDVALUE('Top_Number'[TopN])
RETURN
    CALCULATE (
        COUNTROWS (
            FILTER ( Table2, Table2[RankWithinWeekAndID] <= Top_N && Table2[id] = CurrentID )
        )
    )

     

     

     

    5.Change slicer to single selection:

    6.Here is the result: 

     

     

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.


    Best Regards,
    Fen Ling,


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