Forum Discussion
HELP! Pivot Sorting
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 > 0and make filter on it
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
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
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 helpYou 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 > 0and make filter on it
- Wow, Incredible. THANK YOU!
