March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet 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
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.
Solved! Go to 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!
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.
@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?
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!
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
96 | |
82 | |
69 | |
61 |
User | Count |
---|---|
138 | |
115 | |
115 | |
99 | |
98 |