Forum Discussion
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 | 1
1001 | 1
1002 | 2
1003 | 3
The behavior i need is for the total to be 6, instead of 7. Essentially only counting 1 version of 1001.
I've built 2 measures
Unique Hours = Max(Production data [Production Hours])
&
Total Production Hours = sumx(DISTINCT('Production Data'[Production Order]), [Unique Hours])
My hours are coming up short, my only idea is that the "unique hours" need to be summed, and might being counted instead?
Any Ideas?
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.
12 Replies
aschillinger20 , Try like
Total Production Hours = sumx(values('Production Data'[Production Order]), Max(Production data [Production Hours]))
or
Total Production Hours = sumx(Summarize('Production Data', 'Production Data'[Production Order],"_1", Max(Production data [Production Hours])),[_1])
the first formula is calculating a significantly higher number when i load into my workbook. I should be getting 328, and i'm getting over 13,000.
The second formula i get an error: Column: 'MAX Production Hours' in table 'production data' cannot be found or may not be used in this expression.
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.
