Show top 50 records in a table, the values are from measure

[sridharbabu]

I have a table with ordern , location and a measure(Sales). The table shows only 2 months of data dynamically every month.

I want to show top 50 sales in the table. The sales is a measure.

[Anonymous · Accepted solution]

HI @sridharbabu,

Perhaps you can try to use the following measure formulas and use this on the chart 'visual level filter' to filter records based on flag:

flag =
VAR currOrdern =
    SELECTEDVALUE ( Table1[ordern] )
VAR currlocation =
    SELECTEDVALUE ( Table1[location] )
VAR summary =
    SUMMARIZE ( ALLSELECTED ( Table1 ), [ordern], [location], "_Sales", [Sales] )
VAR filtered =
    TOPN ( 50, summary, [_Sales], DESC )
RETURN
    IF (
        currOrdern
            IN SELECTCOLUMNS ( filtered, "ordern", [ordern] )
                && currlocation IN SELECTCOLUMNS ( filtered, "location", [location] ),
        "Y",
        "N"
    )

Regards,

Xiaoxin Sheng

[sridharbabu]

the measure is working fine. now to add rank to the table based on [sales]. can you help me on this.

[sridharbabu]

This works fine for me, 

flag =
VAR currOrdern =
    SELECTEDVALUE ( Table1[ordern] )
VAR currlocation =
    SELECTEDVALUE ( Table1[location] )
VAR summary =
    SUMMARIZE ( ALLSELECTED ( Table1 ), [ordern], [location], "_Sales", [Sales] )
VAR filtered =
    TOPN ( 50, summary, [_Sales], DESC )
RETURN
    IF (
        currOrdern
            IN SELECTCOLUMNS ( filtered, "ordern", [ordern] )
                && currlocation IN SELECTCOLUMNS ( filtered, "location", [location] ),
        "Y",
        "N"
    )

 an added requirement is if duplicate record exist has to take latest record. and to show serial numbers from 1 to 50. could you please help me in excluding duplicate records.  

[az38]

Hi @sridharbabu 

you can press on your table visual, open filters pane and configure filter like this

[sridharbabu]

i applied top 2 fetch two months data, where i am having month no.  for the sales measure not gettig TOP in filters pane.

[sridharbabu]

and also how to load two months of data in totable dynamically. when the data is added to source table.