Forum Discussion
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
-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?
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
6 Replies
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>1add filter or slicer on this column.
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
