cancel
Showing results 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

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 :

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

1 ACCEPTED SOLUTION
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,
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 2
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,
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

Helper I

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 =
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``````

R/

F.