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 ...
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

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.