Summarizing data without Pivot Table

Question

Hello All,

I have a fields Client, Supply, Quantity and Country.

I need,

Summary for top 10 Clients by country sorting based on the Quantity.

Summary for top 10 Supply by country sorting based on the Quantity.

This we can do it easily by Pivot but,

I have 80+ Countries and i need to create lots of Pivot tables, also need to make sure about the range of the pivot table every week. After refreshing the pivot i am seeing few countries wont sort properly. Also, I need only Top 10 Countries and supplies for all the countries.

Any idea to summarize this data without the pivot table.

If we need to do only with Pivot table then,

How to automatically sort descending after every refresh?

How to make sure only top 10 is only display?

How to make sure the range is covered fully in the data sheet?

Pls. help me understand to summarize this data.

Summary

I have uploaded the file in drive for your quick reference, https://docs.google.com/spreadsheets/d/17AdZB4uv6aL6MU7I6vE9avCluP2H3zXH/edit?usp=sharing&ouid=101018147687121740410&rtpof=true&sd=true

Answer 1

Hi

I told you to download the file I shared... That said I'm not sure I understand what you did so instead of sharing pictures could you share the workbook you're working on?

Thanks

Answer 2

Hi, Sorry for the confusion.

I am unable to download the file, when i click the link it goes to one drive and there it is showing the excel file on view mode as shown in the screen shot below. All the options are disabled, looks like i dnt have access to download the file.

Can you pls. help me understand how the 3 tables were created in "Queries" sheet in your file.

I have posted my file in this link, pls. refer sheet1. Thanks so much for your patience. :-) Link = https://docs.google.com/spreadsheets/d/17AdZB4uv6aL6MU7I6vE9avCluP2H3zXH/edit?usp=sharing&ouid=101018147687121740410&rtpof=true&sd=true

Answer 3

Hi

• To download a file that opens in Excel Web/Online, go to File > Save As > Download a Copy
• Re. Can you pls. help me understand how the 3 tables were created in "Queries" sheet in your file I'm afraid this isn't realistic. If you want to learn Power Query here are some pointers

I looked at a formula based solution (no query) that's available here. It uses an 'HelperSheet' (can be hidden):

• Put your data in table Data (sheet name 'Data')
• Select a country in the drop down list in sheet 'Summary'

==> You'll get the 2 views you expect

Answer 4

Thank you so much for your support. I will try to understand this, thanks again.

Answer 5

Hi

Another option (to download from this link) with DAX Queries (authored with Dax Studio Add-in). 2 Tables:

• Table "Country" in sheet 'Summary'
• Table "Data" in sheet 'Data'

Both Tables are loaded to the Data Model with a One-to-Many relationship

How-To

• Select a country in sheet 'Summary'
• Go to Data tab > Refresh All

DAX Queries

Client:

EVALUATE 

TOPN ( 

    10, 

    SUMMARIZE ( 

        NATURALINNERJOIN ( 

            Data, 

            Country 

        ), 

        Data[Client Name], 

        "Total Qty", SUM ( Data[Quantity] ) 

    ), 

    [Total Qty] 

) 

ORDER BY [Total Qty] DESC 

Supply:

EVALUATE 

TOPN ( 

    10, 

    SUMMARIZE ( 

        NATURALINNERJOIN ( 

            Data, 

            Country 

        ), 

        Data[Supply], 

        "Total Qty", SUM ( Data[Quantity] ) 

    ), 

    [Total Qty] 

) 

ORDER BY [Total Qty] DESC