Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How can I export a Top N table to excel?

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

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

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

     

    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

8 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        5 years ago

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

         

        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

  • negi007's avatar
    negi007
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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.