Excel slicer, pivot table and sorting problem

Question

Want to show dashboard on client using slicer. I want to compare client data for last 2years. for that i used pivot table and % difference to find out the increase or decrease in the amount as compare to last year.

but the problem is where the amount is blank or not available either last year or this year, % difference is blank.

Secondly, when sorting the data on % difference column in descending order, it is not working proper like 115% is showing on 1st row, 1020% is showing on 5th row.

Need solution.

Answer 1

Hi,

* Under the "Show Values As" section, select the "Show items with no data" checkbox. * Click "OK" to apply the settings. 

I already selected “% difference from” in “show value as” then how can I once again select the above mention option.

Answer 2

Hi Asif S!

I'm sorry, unfortunately, I don't have any further troubleshooting to offer.

There are many knowledgeable users active on the forum and I hope that someone else can offer further insight into your issue.

Kind Regards, Shakiru

Answer 3

Hi Asif S!

To address the issues you're facing with the slicer, pivot table, and sorting in Excel, let's go through the following steps:

Handling blank values in the % difference column: * In the pivot table, right-click on any value in the % difference column and select "Value Field Settings." * In the "Value Field Settings" dialog box, select the "Show Values As" tab. * Choose "% Difference From" from the drop-down list and select the appropriate base field and base item (e.g., "Last Year" or "Previous Year"). * In the "Base Field" section, choose the field that represents the amount (e.g., "Amount"). * Under the "Show Values As" section, select the "Show items with no data" checkbox. * Click "OK" to apply the settings. This should ensure that the % difference is calculated even if the amount is blank or not available for either last year or this year.

Sorting the data on the % difference column: * Select any cell within the pivot table. * Go to the "Data" tab in the Excel ribbon and click on the "Sort" button. * In the "Sort" dialog box, choose the % difference column from the "Sort by" drop-down list. * Set the sort order to "Largest to Smallest" if you want to display the highest % difference values first. * Click "OK" to apply the sorting. The pivot table should now display the % difference values in the correct descending order.

Kindly let me know, if you require additional assistance, I will be glad to help further.

Best Regards, Shakiru