Forum Discussion
Solved
Distinct sum based on unique value
I have some production data that duplicates when another user signs into an order. Here is a basic examaple: Productction Order Number | Production Hours 1001 ...
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.
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.