Forum Discussion
Comparing filtered vs non filtered values
I have sales information that I retrieve from SAP tables using Access into Power BI. My tables are separated using hierarchy information (Table 1 - Header Info, Table 2 - Item Level, Table 3 - Types of Conditions used on each Item, Table 4 - Scales for each Condition).
| Sales Order # | Line Item (on sales order) | Part # | Condition Type | Net value | Condition Status (Active or Inactive) |
| 1111111111 | 1 | part A | con 1 | 20.00 | A |
| 1111111111 | 1 | part A | con 2 | 30.00 | I |
| 1111111111 | 1 | part A | con 3 | 40.00 | I |
| 1111111111 | 2 | part B | con 4 | 15.00 | I |
| 1111111111 | 2 | part B | con 2 | 50.00 | A |
| 1111111111 | 2 | part B | con 3 | 40.00 | A |
| 2222222222 | 1 | part C | con 1 | 30.00 | A |
| 2222222222 | 1 | part C | con 2 | 25 | I |
I am trying to get my table to show count summary of line items where "con X" was "A" over other conditions.
My selection criteria is Condition Type and Condition Status (they are on the same table). For example, when I select "con 1" and "A", my output table should be:
| Sales Order # | Line Item (on sales order) | Part # | Condition Type | Net value | Condition Status (Active or Inactive) |
| 1111111111 | 1 | part A | con 1 | 20.00 | A |
| 1111111111 | 1 | part A | con 2 | 30.00 | I |
| 1111111111 | 1 | part A | con 3 | 40.00 | I |
| 2222222222 | 1 | part C | con 1 | 30.00 | A |
| 2222222222 | 1 | part C | con 2 | 25 | I |
Summary Table should show:
# of Sales Order: 2
# of Line Items: 2
Count of con 1: 2
Count of con 2: 2
Count of con 3: 1
The issue that I am running into right now is that when I filter on conditions using visuals, it automatically excludes conditions that don't meet my criteria and output is showing:
| Sales Order # | Line Item (on sales order) | Part # | Condition Type | Net value | Condition Status (Active or Inactive) |
| 1111111111 | 1 | part A | con 1 | 20.00 | A |
| 2222222222 | 1 | part C | con 1 | 30.00 | A |
Summary Table:
# of Sales Order: 2
# of Line Items: 2
Count of con 1: 2
Any help is appreciated.
2 Replies
I am trying to get my table to show count summary of line items where "con X" was "A" over other conditions.
My selection criteria is Condition Type and Condition Status (they are on the same table). For example, when I select "con 1" and "A", my output table should be:
Sales Order # Line Item (on sales order) Part # Condition Type Net value Condition Status
(Active or Inactive)
1111111111 1 part A con 1 20.00 A 1111111111 1 part A con 2 30.00 I 1111111111 1 part A con 3 40.00 I 2222222222 1 part C con 1 30.00 A 2222222222 1 part C con 2 25 I Summary Table should show:
# of Sales Order: 2
# of Line Items: 2
Count of con 1: 2
Count of con 2: 2
Count of con 3: 1
The issue that I am running into right now is that when I filter on conditions using visuals, it automatically excludes conditions that don't meet my criteria and output is showing:
Sales Order # Line Item (on sales order) Part # Condition Type Net value Condition Status
(Active or Inactive)
1111111111 1 part A con 1 20.00 A 2222222222 1 part C con 1 30.00 A The table you shown below seems correct. How exactly you want the filter to work to get table 1
The way I am thinking is to filter data on based on criteria, copy sales order/line item information of the filtered results, reset filters, and apply the filter but this time on sales order/line item that were copied from first filter criteria.
Sorry I am new to Power BI, hence I am not familiar with all the functionalities. I am trying to do this in the most efficient way possible without affecting the report performance.
