Filter a dataset and sum the values without considering the duplicates.

[jmateu88]

Hello,

I am trying to get the sum of pending work by the interval selected (week, month) after filtering the dataset and dropping the duplicates, but I am having problems to get it.

The dataset is the result of a sum of pending work by orders (OT) and its operations (op) where, each week on Monday are added all the orders pending in the system. So, each week if the orders were not done the previous week, they will be duplicated in the system in the new week.

To simplify the understanding and the complexity to only focus on the problem, I created a dataset with only 2 orders (one for each type) and some operations for each order and I put two weeks of January and two of February.

What I had in mind when I was trying to design de measure is, first of all, apply the filters for the target orders (I simplified it to only the type of order) and then, depending of the selection done (by week, by month), the measure must show which orders and its operations are in the interval of time and get the sum.

Just as an example, if the interval selected is February, I would like to check all February, see which orders are for type 83 and then, see the charge and sum it. In this case, if the order is twice, only consider the charge of one operation instead of sum it.

In the picture, it is possible to see that in the measure 'First try', instead of having the result of 10.2 that would be desired being the sum of the different orders-operations, it is getting the max (6) and multiplying by the numbers of rows (5).

the raw data for the example would be this:

Month	Week	OT	Type	op	OT-op	Charge
1	1	8301	83	1550	8301-1550	2
1	1	8301	83	2001	8301-2001	6
1	1	8301	83	2011	8301-2011	0,2
1	1	8301	83	5999	8301-5999	0
1	1	8301	83	7010	8301-7010	2
1	1	8102	81	1500	8102-1500	1
1	1	8102	81	200	8102-200	2
1	1	8102	81	3000	8102-3000	4
1	2	8301	83	1550	8301-1550	2
1	2	8301	83	2001	8301-2001	6
1	2	8301	83	2011	8301-2011	0,2
1	2	8301	83	5999	8301-5999	0
1	2	8301	83	7010	8301-7010	2
1	2	8102	81	1500	8102-1500	1
1	2	8102	81	200	8102-200	2
1	2	8102	81	3000	8102-3000	4
2	6	8102	81	1500	8102-1500	1
2	6	8102	81	200	8102-200	2
2	6	8102	81	3000	8102-3000	4
2	6	8301	83	1550	8301-1550	2
2	6	8301	83	2001	8301-2001	6
2	6	8301	83	2011	8301-2011	0,2
2	6	8301	83	5999	8301-5999	0
2	6	8301	83	7010	8301-7010	2
2	7	8102	81	1500	8102-1500	1
2	7	8102	81	200	8102-200	2
2	7	8102	81	3000	8102-3000	4
2	7	8301	83	1550	8301-1550	2
2	7	8301	83	2001	8301-2001	6
2	7	8301	83	2011	8301-2011	0,2
2	7	8301	83	5999	8301-5999	0
2	7	8301	83	7010	8301-7010	2

Thank you in advance. 

Regards.

[jmateu88]

Hello @Alex87 and I am sorry if I did not explain well the issue.

I see that the solution if we check the graph is totally valid. I am sorry for not checking it.

However, the solution does not suits if we see the result in the table as long as more months in the table for the same orders, it will increase the charge of them (this will not happen in the first try after being modified).

I attach the photo to make it easier. Since there are two months, the charges doubles

Anyway, I obtained the solution so I close this issue.

Thank you very much for the reply Alex.

[Alex87]

@jmateu88 , I am sorry, but I do not understand how the solution provided is not the "right" solution per your described requirements.

Please look below: I am using the provided measure calculation and the figures are correct per your request. It does not matter if 1 week or two weeks is selected on a specific month. You just need to use a slicer that is single selected on a month. At least that is what I undestood when you wrote:.

"if the interval selected is February, I would like to check all February, see which orders are for type 83 and then, see the charge and sum it"

If I did not understand correctly the full scope of requirements, please provide additional information and expectations. Please describe what is the expectation when more than one month is selected?

[Alex87 · Accepted solution]

Hello @jmateu88 ,

Is this answering your query? I built in a variable a virtual table where I do not include weeks, and filter on a type (in this case 83), and then performing the sum. 

Third_Try = 
VAR _UniqueRows = 
    SUMMARIZE(
        FILTER(FilteredDataset, FilteredDataset[Type] = 83),
        FilteredDataset[Month],
        FilteredDataset[OT],
        FilteredDataset[op],
        "@TotalCharge", AVERAGE(FilteredDataset[Charge])
    )
RETURN
    SUMX(_UniqueRows, [@TotalCharge])

if yes, please mark my reply as the solution. thanks!

[jmateu88]

Hello @Alex87 ,

thank you for your answer and for give me the idea to a partial solution.

The problem with the formula you provided is that is doubling the time by month. If the week was added, then, it would count as a CALCULATED, filtering the type by = 83.

Howerver you gave me the idea with the average. If I change the MAX to AVERAGE, the result is what I was looking for.

I attached the photo where we can see that the sum is right and your proposal doubles the time twice because of the two weeks in the month. Again, thank you very much.

 

I let open for the moment the post because I do not consider this as the 'right' solution. It must be somehow in power bi of filtering and then, removing duplicates to get the sum.