Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
jmateu88
Helper I
Helper I

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

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.

 

photo.png

 

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:

MonthWeekOTTypeopOT-opCharge
1183018315508301-15502
1183018320018301-20016
1183018320118301-20110,2
1183018359998301-59990
1183018370108301-70102
1181028115008102-15001
118102812008102-2002
1181028130008102-30004
1283018315508301-15502
1283018320018301-20016
1283018320118301-20110,2
1283018359998301-59990
1283018370108301-70102
1281028115008102-15001
128102812008102-2002
1281028130008102-30004
2681028115008102-15001
268102812008102-2002
2681028130008102-30004
2683018315508301-15502
2683018320018301-20016
2683018320118301-20110,2
2683018359998301-59990
2683018370108301-70102
2781028115008102-15001
278102812008102-2002
2781028130008102-30004
2783018315508301-15502
2783018320018301-20016
2783018320118301-20110,2
2783018359998301-59990
2783018370108301-70102


Thank you in advance. 

Regards.



1 ACCEPTED SOLUTION
Alex87
Super User
Super User

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!




Did I answer your question? Mark my post as a solution!
Appreciate your Like/Kudos

Proud to be a Super User!




View solution in original post

4 REPLIES 4
jmateu88
Helper I
Helper I

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

Sin título.png

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

Thank you very much for the reply Alex.

Alex87
Super User
Super User

@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_1-1714669579852.png

 

 




Did I answer your question? Mark my post as a solution!
Appreciate your Like/Kudos

Proud to be a Super User!




Alex87
Super User
Super User

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!




Did I answer your question? Mark my post as a solution!
Appreciate your Like/Kudos

Proud to be a Super User!




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.

Sin título.png

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.