SOLVED

How to filter out data in a power pivot table without values?

%3CLINGO-SUB%20id%3D%22lingo-sub-1764616%22%20slang%3D%22en-US%22%3EHow%20to%20filter%20out%20data%20in%20a%20power%20pivot%20table%20without%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1764616%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20all.%20I'd%20like%20to%20filter%20out%20data%20in%20a%20power%20pivot%20table%20that%20does%20not%20have%20values.%20In%20the%20attached%20picture%2C%20any%20rows%20that%20do%20not%20have%20dollars%20in%20the%20sum%20of%20amount%22%20column%20I%20want%20removed.%20What's%20the%20best%20way%20to%20accomplish%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20any%20help%20provided.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPaul%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(1).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F225431iF36B45D32B35163F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%20(1).png%22%20alt%3D%22Screenshot%20(1).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1764616%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1764696%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20filter%20out%20data%20in%20a%20power%20pivot%20table%20without%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1764696%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676026%22%20target%3D%22_blank%22%3E%40slohombre%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20apply%20a%20%22%3CSTRONG%3EDoes%20Not%20Equal%3C%2FSTRONG%3E%22%20%3CSTRONG%3E0%3C%2FSTRONG%3E%20%22%3CSTRONG%3EValues%20Filters%3C%2FSTRONG%3E%22%20on%20Row%20Labels%20to%20hide%20the%20Row%20Labels%20where%20Sales%20of%20amount%20is%200.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1764758%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20filter%20out%20data%20in%20a%20power%20pivot%20table%20without%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1764758%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Paul%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676026%22%20target%3D%22_blank%22%3E%40slohombre%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20right%20click%20on%20one%20of%20the%20Row%20Labels%20in%20your%20left-hand%20column%20and%20then%20go%20to%20Filter%20and%20Value%20Filters%20Choose%20Sum%20Of%20Amount%20Does%20not%20equal%200%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22onethreeone_0-1602257046747.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F225438iF0EA7F1E9A30FA52%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22onethreeone_0-1602257046747.png%22%20alt%3D%22onethreeone_0-1602257046747.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22onethreeone_1-1602257105731.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F225439iA966B1635F35138E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22onethreeone_1-1602257105731.png%22%20alt%3D%22onethreeone_1-1602257105731.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThat%20should%20sort%20it%20for%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGood%20luck!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1765112%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20filter%20out%20data%20in%20a%20power%20pivot%20table%20without%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1765112%22%20slang%3D%22en-US%22%3EThank%20you!%20That%20did%20the%20trick.%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi, all. I'd like to filter out data in a power pivot table that does not have values. In the attached picture, any rows that do not have dollars in the sum of amount" column I want removed. What's the best way to accomplish this?

 

Thanks for any help provided.

 

PaulScreenshot (1).png

4 Replies
Highlighted

@slohombre 

You may apply a "Does Not Equal" 0 "Values Filters" on Row Labels to hide the Row Labels where Sum of amount is 0.

 

 

 

Highlighted
Best Response confirmed by slohombre (Occasional Contributor)
Solution

Hi Paul @slohombre 

 

If you right click on one of the Row Labels in your left-hand column and then go to Filter and Value Filters Choose Sum Of Amount Does not equal 0

onethreeone_0-1602257046747.pngonethreeone_1-1602257105731.png

That should sort it for you.

 

Good luck!

Highlighted
Thank you! That did the trick.
Highlighted

@slohombre Glad to hear it. Pivot tables can be tricky beasts in that they don't quite operate like the rest of excel. there's usually a simple solution but it can be a painful process finding out!

 

Good luck!