Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Falongi_82
Helper I
Helper I

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 :

 

1.PBI_0621.png

 

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]
)
)
)
 
Falongi_82_1-1623139866869.png

 

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

 

R/

Fabri

 

 

1 ACCEPTED SOLUTION
v-deddai1-msft
Community Support
Community Support

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

View solution in original post

2 REPLIES 2
v-deddai1-msft
Community Support
Community Support

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

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 😞

 

pbi2206.png

 

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.

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.