Filtering issues

[BIuser45]

I have some trouble with filtering.

I have three tables: Predicted Sales Amount, Actual Sales Amount and Actual Sales Purpose, combined together called Total Table. The Total Table has inherited all the columns from the three tables. I There is a column called Product Type in the Actual Sales Amount table. Only the Actual Sales Amount table contains the product type column. I combined them together because I want the total projected sales amount (Predicted sales amount + actual sales amount). The Actual Sales Purpose table has a column of sales purposes from actual sales. The Predicted Sales Amount table has a column that indicates the sales purpose from predicted sales. Only the Predicted Sales Amount table contains the predicted status column. The combined table has a column that has the sales purpose combined from the Actual Sales Purpose and Predicted Sales Amount table called Combined Purpose. The Total Table could show filter the sales purposes for the total projected sales amount, but it could not filter the product type for the actual sales amount and then add the predicted sales amount to create the total projected sales amount. What happens is when I filter the sales purpose and combined purpose on the Total Table, it only generates the actual sales amount and not the total projected sales amount. Similarly, when I filter the predicted status and the combined purpose on the Total Table, it only generates the predicted sales amount and not the total projected sales amount. Like it resets the filter and everything. Why is that?

What I want is the, filtered product type on the actual sales amount + the filtered predicted status on the predict sales amounts = total projected sales or filtered product type on the actual sales amount + the predict sales amounts = total projected sales or actual sales amount + the filtered predicted status on the predict sales amounts = total projected sales. All three cases with the combined purpose column filtered. Is there a way to do that?

[Anonymous]

Hi @BIuser45 ,

Can you provide us some expected result and sample data. Thank you.

Best Regards

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BIuser45]

Hi v-xiandat-msft, here is the sample data below, which might not make sense, but with real data it is something similar.

There are actual sales table, customer table, actual sales purpose table, predicted sales table, total sales table which are power query data. Those are sample data.

Actual Sales Table, PowerQuery (PQ)

Sales ID	Product Type	Customer ID	Finance Type	Sales Amount	Actual Sales Amount	Combined Sales Amount	Saving ID	Fund ID	Interest ID
1	Electronics	32		1000
2	Hardware	56		2000
3	Saving Plans	78	Yes	3500	3500	3500	1
4	Hedge Funds	89	Yes	3500	3500	3500		2
5	Bonds	90	Yes	3000	3000	3000			3

Customer Table, PQ:

Customer ID
32
56
78
89
90
12
34
35

Actual Sales Purpose Table (PQ)

Sales ID	Sales Purpose ID	Sales Purpose	Combined Purpose
3	1	Savings	Savings
4	2	Fund	Fund
5	3	Interest	Interest

Predicted Sales Table, PQ

Predicted Sales ID	Customer ID	Sales Purpose	Predicted Status	Combined Purpose	Uncomfirmed Sales	Predicted Sales/Expected Sales	Combined Sales	Saving ID	Fund ID	Interest ID
1	45	Fund	Declined	Fund	12345
2	78	Interest	No Interest	Interest	23456
3	12	Savings	Finalized	Savings	26000	26000	26000	76
4	34	Fund	In Progress	Fund	70000	70000	70000		56
5	35	Interest	Early	Interest	30000	30000	30000			23

Total Table, PQ:

Sales ID	Product Type	Customer ID	Finance Type	Sales Amount	Actual Sales Amount	Combined Sales Amount	Saving ID	Fund ID	Interest ID	Predicted Sales ID	Customer ID	Predicted Status	Combined Purpose	Uncomfirmed Sales	Predicted Sales/Expected Sales
1	Electronics	32		1000
2	Hardware	56		2000
3	Saving Plans	78	Yes	3500	3500	3500	1						Savings
4	Hedge Funds	89	Yes	3500	3500	3500		2					Fund
5	Bonds	90	Yes	3000	3000	3000			3				Interest
										1	45	Declined	Fund	12345
										2	78	No Interest	Interest	23456
						26000	76			3	12	Finalized	Savings	26000	26000
						70000		56		4	34	In Progress	Fund	70000	70000
						30000			23	5	35	Early	Interest	30000	30000

The remaining four tables are the results of the total sales table that I want.

Total Table Result 1

Purpose	Actual Sales	Predicted sales	Combined/Projected Sales	%
Savings	10000	26000	36000	0.18367
Interest	20000	30000	50000	0.2551
Fund	40000	70000	110000	0.56122
Total	70000	126000	196000	1

The first total table result is if I do not filter anything.

Total Table Result 2

Purpose	Actual Sales	Predicted sales	Combined/Projected Sales	%
Savings	10000	26000	36000	0.18367
Interest	20000	30000	50000	0.2551
Fund	40000	70000	110000	0.56122
Total	70000	126000	196000	1

The second result is if I only filter sales product type from the total table as saving plans and hedge funds.

Total Table Result 3

Purpose	Actual Sales	Predicted sales	Combined/Projected Sales	%
Savings	10000	26000	36000	0.18367
Interest	20000	30000	50000	0.2551
Fund	40000	70000	110000	0.56122
Total	70000	126000	196000	1

Third result is if I only filter the predicted status from the total table as finalized.

Total Table Result 4

Purpose	Actual Sales	Predicted sales	Combined/Projected Sales	%
Savings	3500	26000	29500	0.46825
Interest	3500	30000	33500	0.53175
Total	7000	56000	63000	1

Fourth result is if I only filter the purpose of the total table.