Forum Discussion

Rosy_888's avatar
Rosy_888
Copper Contributor
Jun 29, 2021
Solved

Using Pivot table with filtering by conditions

Hello, I use pivot table to calculate orders, prices and total for each state like the picture below. I want to add a condition for Orders such as less than 94. This means all data would be changed ...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Jun 29, 2021

    Rosy_888 

    You would like to filter source data. PivotTable ignores filtering of the source and works on entire data. Workarounds could be

    - create new filtered table and build PivotTable on it, sample is here Pivot Table from Filtered List Visible Rows – Contextures Blog

    - load parameter to data model, e.g. with Power Query, create DAX measure which uses this parameter to sum data and build PivotTable on data model using such measure 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 29, 2021

Rosy_888 

You would like to filter source data. PivotTable ignores filtering of the source and works on entire data. Workarounds could be

- create new filtered table and build PivotTable on it, sample is here Pivot Table from Filtered List Visible Rows – Contextures Blog

- load parameter to data model, e.g. with Power Query, create DAX measure which uses this parameter to sum data and build PivotTable on data model using such measure 

Resources