Forum Discussion

Falongi_82's avatar
Falongi_82
Helper I
5 years ago
Solved

Dynamic Top N - Help

Hi All,

 

Based on the following example (https://community.powerbi.com/t5/Community-Blog/Top-N-with-Slicer-and-Steps/ba-p/952240), I try to implement it on my report but it doesn't work.

I can't find where it is blocking, maybe the relationships or my measures.

I would like based on a selection list, display my top N materials based on their total lines.

 

See below my tables :

 

 

Mesure and table created following the post :

 

TopN Steps = {3, 5, 10, 20}
 
TopN Union = UNION(ROW("Key Values", -99, "Display Values", "Others"), ALLNOBLANKROW(T_MATERIAL[MATERIAL_CODE],T_MATERIAL[MAT_CODE_DESCRIPTION]))
 
==> Perhaps my problem come from the union because there is no link whith material found in the DIM_DUMP table!
I don't know ! 😞
 
Top X Steps =
VAR TopNumber = IF(HASONEVALUE('TopN Steps'[TopN]),VALUES('TopN Steps'[TopN]),3)
VAR Rest = COUNTROWS ( DIM_DUMP ) - TopNumber
RETURN
IF (
HASONEVALUE ( 'TopN Union'[Key values] ),
CALCULATE (
[M_TOT_LINE],
FILTER (
DIM_DUMP,
DIM_DUMP[MATERIAL_CODE] = VALUES ( 'TopN Union'[Key values] )
&& CONTAINS (
TOPN (
TopNumber,
ADDCOLUMNS (
ALL ( 'TopN Union'[Key values] ),
"Tot Lines", CALCULATE (
[M_TOT_LINE],
FILTER ( DIM_DUMP, DIM_DUMP[MATERIAL_CODE] = EARLIER ( [Key values] ))
)
),
[M_TOT_LINE], DESC
),
'TopN Union'[Key values], VALUES ('TopN Union'[Key values])
)
)
)
)
 
--------------------------------------------------------------------
 
Other Steps =
VAR TopNumber =
IF ( HASONEVALUE ( 'TopN Steps'[TopN] ), VALUES ( 'TopN Steps'[TopN] ), 8 )
VAR Rest =
COUNTROWS ( DIM_DUMP ) - TopNumber
RETURN
IF (
HASONEVALUE ( 'TopN Union'[Key values] ),
IF (
VALUES ( 'TopN Union'[Display values] ) = "Others",
SUMX (
TOPN (
Rest,
ADDCOLUMNS (
VALUES ( DIM_DUMP[MATERIAL_CODE] ),
"Tot Lines", [M_TOT_LINE]
),DIM_DUMP[M_TOT_LINE]
,ASC
),
DIM_DUMP[M_TOT_LINE]
)
)
)
 

 

thx in advance for your help and explaination that improve my knowledge

 

R/

Fabri

 

 

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi Falongi_82 ,

     

    In DIM_DUMP table, you have duplicated MATERIAL_CODE, so you can't use countrows ,you should use distinctcount.

     

    I did some little modification on your measures:

     

    Top X Steps =
VAR TopNumber =
    MAX ( 'TopN Steps'[TopN] )
VAR Rest =
    DISTINCTCOUNT ( DIM_DUMP[MATERIAL_CODE] ) - TopNumber
RETURN
    CALCULATE (
        [M_TOT_LINE],
        FILTER (
            DIM_DUMP,
            DIM_DUMP[MATERIAL_CODE] = MAX ( 'TopN Union'[Key values] )
                && CONTAINS (
                    TOPN (
                        TopNumber,
                        ADDCOLUMNS (
                            ALL ( 'TopN Union'[Key values] ),
                            "Tot Lines",
                                CALCULATE (
                                    [M_TOT_LINE],
                                    FILTER ( DIM_DUMP, DIM_DUMP[MATERIAL_CODE] = EARLIER ( [Key values] ) )
                                )
                        ),
                        [M_TOT_LINE], DESC
                    ),
                    'TopN Union'[Key values], MAX ( 'TopN Union'[Key values] )
                )
        )
    )

Other Steps =
VAR TopNumber =
    MAX ( 'TopN Steps'[TopN] )
VAR Rest =
    DISTINCTCOUNT ( DIM_DUMP[MATERIAL_CODE] ) - TopNumber
RETURN
    IF (
        VALUES ( 'TopN Union'[Display values] ) = "Others",
        SUMX (
            TOPN (
                Rest,
                ADDCOLUMNS ( VALUES ( DIM_DUMP[MATERIAL_CODE] ), "Tot Lines", [M_TOT_LINE] ),
                DIM_DUMP[M_TOT_LINE], ASC
            ),
            DIM_DUMP[M_TOT_LINE]
        )
    )

     

    If the issue is still existing. you'd better show us the sample pbix.

     

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

     

    Best Regards,

    Dedmon Dai

2 Replies

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    Hi Falongi_82 ,

     

    In DIM_DUMP table, you have duplicated MATERIAL_CODE, so you can't use countrows ,you should use distinctcount.

     

    I did some little modification on your measures:

     

    Top X Steps =
VAR TopNumber =
    MAX ( 'TopN Steps'[TopN] )
VAR Rest =
    DISTINCTCOUNT ( DIM_DUMP[MATERIAL_CODE] ) - TopNumber
RETURN
    CALCULATE (
        [M_TOT_LINE],
        FILTER (
            DIM_DUMP,
            DIM_DUMP[MATERIAL_CODE] = MAX ( 'TopN Union'[Key values] )
                && CONTAINS (
                    TOPN (
                        TopNumber,
                        ADDCOLUMNS (
                            ALL ( 'TopN Union'[Key values] ),
                            "Tot Lines",
                                CALCULATE (
                                    [M_TOT_LINE],
                                    FILTER ( DIM_DUMP, DIM_DUMP[MATERIAL_CODE] = EARLIER ( [Key values] ) )
                                )
                        ),
                        [M_TOT_LINE], DESC
                    ),
                    'TopN Union'[Key values], MAX ( 'TopN Union'[Key values] )
                )
        )
    )

Other Steps =
VAR TopNumber =
    MAX ( 'TopN Steps'[TopN] )
VAR Rest =
    DISTINCTCOUNT ( DIM_DUMP[MATERIAL_CODE] ) - TopNumber
RETURN
    IF (
        VALUES ( 'TopN Union'[Display values] ) = "Others",
        SUMX (
            TOPN (
                Rest,
                ADDCOLUMNS ( VALUES ( DIM_DUMP[MATERIAL_CODE] ), "Tot Lines", [M_TOT_LINE] ),
                DIM_DUMP[M_TOT_LINE], ASC
            ),
            DIM_DUMP[M_TOT_LINE]
        )
    )

     

    If the issue is still existing. you'd better show us the sample pbix.

     

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

     

    Best Regards,

    Dedmon Dai

    • Falongi_82's avatar
      Falongi_82
      Helper I
      5 years ago

      Hi Dedmon Dai,

       

      I've change all my measure and follow this tuto find https://www.sqlbi.com/articles/filtering-the-top-products-alongside-the-other-products-in-power-bi/

       

      This, seems to work but not always 😞

      When for a category, I'dont have enough articles to top, the result is wrong :

      see below example with Top 10 for swap proactive category 😞

       

       

      My measures :

       

      M_T_QTY = 
VAR QtyOfAll =
    CALCULATE([M_TOT_QTY], REMOVEFILTERS(T_MATERIAL_CODE_test[MAT_CODE_DESCRIPTION]))
RETURN
    If (
        ISINSCOPE(T_MATERIAL_CODE_test[MAT_CODE_DESCRIPTION]), 
        VAR ProductsToRank = [TopN Value]
        VAR IsOtherSelected = SELECTEDVALUE(T_MATERIAL_CODE_test[MAT_CODE_DESCRIPTION]) = "Others"
        VAR ProductsWithQtyTot =
            ADDCOLUMNS(
                ALLSELECTED(T_MATERIAL_CODE_test[MAT_CODE_DESCRIPTION]),
                "@QtyTot",[M_TOT_QTY]
            )
        VAR TopNProds = TOPN(ProductsToRank,ProductsWithQtyTot,[@QtyTot])
        VAR QtyOfTopN = sumx(TopNProds,[@QtyTot])
        VAR Result = IF(IsOtherSelected, QtyOfAll-QtyOfTopN,[M_TOT_QTY])

        RETURN Result, QtyOfAll)
      M_RANKING = 
VAR ProductsToRank = [TopN Value]
VAR Ranking = RANKX(ALLSELECTED(T_MATERIAL_CODE_test[MAT_CODE_DESCRIPTION]),[M_TOT_QTY])
VAR IsOtherSelectect = SELECTEDVALUE(T_MATERIAL_CODE_test[MAT_CODE_DESCRIPTION] ) = "Others"
VAR Result = 
    IF(
        IsOtherSelectect,
        ProductsToRank+1 ,
        IF(Ranking<=ProductsToRank,Ranking,Ranking+1)
    )
RETURN
Result
      M_ROW_VISIBLE = 
VAR Ranking = [M_RANKING]
VAR TopNValue = [TopN Value]+1
VAR Result = INT( Ranking <= TopNValue)
RETURN
    Result

       

      thx in advance for your help,

      R/

      F.