SOLVED

HELP! Pivot Sorting

%3CLINGO-SUB%20id%3D%22lingo-sub-2592748%22%20slang%3D%22en-US%22%3EHELP!%20Pivot%20Sorting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2592748%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20very%20large%20data%20file%20and%20I%20am%20trying%20to%20sort%20but%20having%20trouble.%3C%2FP%3E%3CP%3EThe%20pivot%20table%20looks%20like%20this%26nbsp%3B%3C%2FP%3E%3CP%3E________________%3C%2FP%3E%3CP%3E-91%3C%2FP%3E%3CP%3E%26nbsp%3B%20-100040%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B-USA%3C%2FP%3E%3CP%3E%26nbsp%3B%20-100200%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B-USA%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B-United%20Kingdom%3C%2FP%3E%3CP%3E%26nbsp%3B%20-123402%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B-USA%3C%2FP%3E%3CP%3E%26nbsp%3B%20-142389%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B-USA%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B-France%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eetc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20only%20want%20to%20view%20trip%20%23%20(ex%3A142389)%20with%20multiple%20countries.%20Trip%20%23%20with%20just%20USA%20I%20do%20not%20want%20to%20see.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20any%20way%20to%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2592748%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2593214%22%20slang%3D%22en-US%22%3ERe%3A%20HELP!%20Pivot%20Sorting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2593214%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1113959%22%20target%3D%22_blank%22%3E%40Samuel1720%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CP%3EFor%20such%20model%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20492px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F298802iFB58D7155657072F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ecreating%20PivotTable%20add%20data%20to%20data%20model%20and%20using%20DAX%20add%20calculated%20column%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DVAR%20No%3D%5B%23%5D%20VAR%20qty%3DCALCULATE(COUNTROWS(Table1)%2C%20ALL(Table1)%2C%20Table1%5B%23%5D%3DNo)%20RETURN%20%20qty%26gt%3B1%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eadd%20filter%20or%20slicer%20on%20this%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a very large data file and I am trying to sort but having trouble.

The pivot table looks like this 

________________

-91

  -100040 

     -USA

  -100200

     -USA

     -United Kingdom

  -123402

     -USA

  -142389

     -USA

     -France

 

etc.

 

I only want to view trip # (ex:142389) with multiple countries. Trip # with just USA I do not want to see.

 

Is there any way to do this?

 

 

6 Replies

@Samuel1720 

As variant

For such model

image.png

creating PivotTable add data to data model and using DAX add calculated column

=VAR No=[#] VAR qty=CALCULATE(COUNTROWS(Table1), ALL(Table1), Table1[#]=No) RETURN  qty>1

add filter or slicer on this column.

@Sergei Baklan Thank you so much!

I am having trouble implementing this into my own table though

As I read about calculated columns, I did not understand them completely.

 

how did you add your calculated column and then add the slicer? 

 

so far, I have my pivot table set up just how you made yours. I am just unsure how and where to enter the formula to make a calculated column. 

 

I have tried to make a calculated field but i do not think that is the right place, and have tried to enter the formula into the top of the pivot table column but it does not let me enter it. I then thought it was cause the pivot table name is different but changing that did not work either... Thank you for your help this is very advanced for me

@Samuel1720 

First, creating PivotTable add your data to data model

image.png

I assume you are on Windows and on Excel which supports data model.

Second, be sure Power Pivot is activated. That is File -> Options -> Add-ins -> COM Add-ins

image.png

and here

image.png

Open it (Data -> Manage data model)

image.png

Add new column here to the table

image.png

Thank you so much for this because I am learning a lot and quite amazed at how much excel can do.

The next issue (Im so sorry... thank you for your time) is
-91
-100040
-USA
-USA
-100200
-USA
-USA
-UK
-123402
-USA
-142389
-USA
-France

-156789

-France
etc

how can I tell excel to differentiate trip # that are USA and another country. If it is just USA or multiple USA I do not want to see it (same if it is just one foreign country or multiple foreign countries). Only trip # that have USA (either once or multiple) AND another country (once or multiple)... I have a feeling it may be an if function but am unsure how to tell excel

 

***in the image provided, I only want to see trip # such as 101544, 101755, 101783.. trip # such as 101433 with multiple USA or other trip # with multiple Foreign countries and no USA I do not want to see... they have to have both


Thank you again for your help

best response confirmed by Samuel1720 (New Contributor)
Solution

@Samuel1720 

You may add column (let say "not only USA") as

=
VAR No = [#]
VAR c = [Country]
VAR withoutUSA =
    CALCULATE (
        COUNTROWS ( Table1 ),
        ALL ( Table1 ),
        Table1[#] = No,
        Table1[Country] <> "USA"
    )
VAR withUSA =
    CALCULATE (
        COUNTROWS ( Table1 ),
        ALL ( Table1 ),
        Table1[#] = No,
        Table1[Country] = "USA"
    )
RETURN
    withoutUSA > 0
        && withUSA > 0

and make filter on it

image.png

Wow, Incredible. THANK YOU!