Forum Discussion
Distinct sum based on unique value
aschillinger20 - Sorry man, missed a paren placement.
Measure = SUMX(SUMMARIZE('Production data',[prod_order_number],"_PRODUCTION HOURS", MAX([P21_hour_qty])),[_PRODUCTION HOURS])That should be the right one.
aschillinger20 - Sorry man, missed a paren placement.
Measure = SUMX(SUMMARIZE('Production data',[prod_order_number],"_PRODUCTION HOURS", MAX([P21_hour_qty])),[_PRODUCTION HOURS])That should be the right one.
So this worked, but is having an odd side effect. The total prodcution hours is totalling properly now at 328 hours.
I have a column that lists the person responsible for the order, when i add the "Maker" into a matrix, the total still shows 328 hours, but if i manually add up the hours next to each maker it totals 423. Any reason the forumula would correctly come up with total, but not allocate properly for each "maker"?
aschillinger20 - Usually it is the other way around. Can you give me a better sample data set and I'll test?
Greg_Deckler sent link to workbook in PM.
I think I understand the problem. The formula you provided calculates properly when no maker is involved.
Once the "maker" is involved it allocates the proper time to both "makers" which inflates the total number by "maker" over the total number.
So I marked this as solved because the orginal question is good.
As a follow up; any ideas how to create a measure that splits the "production hours" between the "maker" when there are duplicates? I was thinking some sort of measure that takes the max hours we just found, and divides by the number of times the production order occurs. Production orders with 1 entry would allocate 100% to one maker, orders with 2 would allocate 50% to each.
This would solve the issue where the sum of maker data exeeds the real total.
It's way past my skill set. Any ideas?