Forum Discussion
aschillinger20
Helper II
Helper IIDistinct 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.
Greg_Deckler
Community Champion
Community Championaschillinger20 - Use SUMMARIZE perhaps:
Measure = SUMX(SUMMARIZE('Table',[Production Order Number],"__Production Hours",MAX([Production Hours]),[__Production Hours])
aschillinger20Helper II
Helper II
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.
- Greg_Deckler
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.