How can I export a Top N table to excel?

[Anonymous]

Hi awesome people!

Is there a way I can export my Top N table to excel as is?

I want to export the Top 15 (or whichever value I choose on my slicer), but the exported data always show the whole rows (400 rows instead of 15). Is there a way to fix this?

Thank you so much!!!

[negi007]

@Anonymous This is possible, if you can add a column in your data like "Top15" and "Bottom Remaining" based on the value. Then you use this new column in your slicer to filter top15. This way when you export data from your visual, it will only export data in the visual for top15. 

[Jihwan_Kim]

Hi, @Anonymous 

Please correct me if I wrongly understood your question.

- create a topn table visualization like below (sample pbix file's link is down below.) by using the below measure sample.

Qty Total TopN =
VAR topnselect =
SELECTEDVALUE ( Parameter[Parameter] )
RETURN
SUMX (
KEEPFILTERS (
TOPN (
topnselect,
ALL ( 'Table'[Product] ),
CALCULATE ( SUM ( 'Table'[Qty] ) ), DESC
)
),
CALCULATE ( SUM ( 'Table'[Qty] ) )
)

- Click the three dots on the visualization and select "export data".

- Then, the exported data will only show the topN table in excel (csv file).

https://www.dropbox.com/s/ti9b17v9htj8srr/laurice.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Hi @Jihwan_Kim ,

This is exactly what I'm trying to achieve!!! Thanks a lot!!

However, I can't seem to make it work on my measure. I tried to copy your TopN measure, but the SUM isn't accepting my [Ave cs/mo] measure. Here's my TopN:

Top Stores = 
CALCULATE( [Ave Cases/mo],
    TOPN( Parameter[Parameter Value], ALL( 'PG MPO'[STORE NAME] ), [Ave Cases/mo], DESC ),
        VALUES( 'PG MPO'[STORE NAME] ) )

The dax I used for Ave cs/mo is:

Ave Cases/mo = 
SUMX(VALUES('PG MPO'[STORE NAME]),
    CALCULATE(AVERAGEX(VALUES('PG MPO'[PeriodName]),[Total Cases])))

Is there a way we can incorporate the FILTER function on my TOPN measure?

Thank you so much! 🙂

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Thank you for your feedback.

I tried to create a QTY total measure, and put it into the TopN measure.

Please check the below picture and the link down below.

The newly created measures are Qty Total and Qty Total TopN V2.

Qty Total TopN V2 =

VAR topnselect =

SELECTEDVALUE ( Parameter[Parameter] )

RETURN

CALCULATE (

[Qty Total],

KEEPFILTERS ( TOPN ( topnselect, ALL ( 'Table'[Product] ), [Qty Total], DESC ) )

)

https://www.dropbox.com/s/ti9b17v9htj8srr/laurice.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Hi @Jihwan_Kim ,

It works!! It works!! 

You're amazing! Thank you so much!😊

Also I noticed that when I put more filters on the Rows such as Store Code and Region, it will export the whole thing again. But when I just keep the Store Name on the Rows, it will only export the Top N rows. Would you mind explaining why is that so?

Thank you so much!! 

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your feedback.

I think it is because of ALL ( 'Table'[Product] ) inside the TOPN.

It only considers the rank of the product column.

If you want the measure to consider more than one column, I think it has to be written differently. 

Thank you.

[Anonymous]

Thank you for this feedback @Jihwan_Kim! 😊

I was able to edit based on your comments and the export works for multiple rows too 🙂

I added more filters on the ALL function. Here's the result when I export:

% Ave CS TopN Filter = 
CALCULATE(
    [% Ave Cs TopN],
    KEEPFILTERS(
        FILTER(
            ALL('PG MPO'[STORE NAME],'PG MPO'[STORE CODE],'PG MPO'[REGION]),
            [Ave Cases/mo])))

Thank you so so much!! You've been a great help!! 🤗

[amitchandak]

@Anonymous , when you export data in power bi service. You have option for summarize data

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-export-data