Forum Discussion

Samuel1720's avatar
Samuel1720
Copper Contributor
Jul 27, 2021
Solved

HELP! Pivot Sorting

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 ...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Jul 28, 2021

    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

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 27, 2021

Samuel1720 

As variant

For such model

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.

  • Samuel1720's avatar
    Samuel1720
    Copper Contributor
    Jul 27, 2021

    SergeiBaklan 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

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jul 28, 2021

      Samuel1720 

      First, creating PivotTable add your data to data model

      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

      and here

      Open it (Data -> Manage data model)

      Add new column here to the table

      • Samuel1720's avatar
        Samuel1720
        Copper Contributor
        Jul 28, 2021

        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