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 - Use SUMMARIZE perhaps:
Measure = SUMX(SUMMARIZE('Table',[Production Order Number],"__Production Hours",MAX([Production Hours]),[__Production Hours])the first option you provided seems to be taken the higest number of hours from all production orders and assigning that number to all production orders. How do i get it to sum the highest production hours by specific production order to sum?
I think we're on the same page, i'm just struggling to follow the formula.
aschillinger20 - I'll try to break it down:
Measure = VAR __Table = SUMMARIZE('Table',[Production Order Number],"__Production Hours",MAX([Production Hours]) // this summarizes the table by Production Order Number so that you have distinct production order numbers and tacks onto this summarization a column called [__Production Hours] which has the MAX of the underlying [Product Hours] column for that distinct Production Order Number. You could have used AVERAGE here or MIN as well for this aggregation since the information is duplicated RETURN SUMX(__Table,[__Production Hours]) // Iterate over the summarized table, summing the __Production Hours column.
First; thank you for your help and patience. I seem to be struggling pretty hard on this one. To clarify; my "Production Hours" column is labled "P21_Hour_Qty", my table is called "Production data"
Here is my formula:
Measure = SUMX(SUMMARIZE('Production data',[prod_order_number],"_PRODUCTION HOURS", MAX([P21_hour_qty]),[_PRODUCTION HOURS]))I'm getting a too few arguments error.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"?
