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

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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

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

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