Distinct sum based on unique value

[aschillinger20]

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? 

[Greg_Deckler]

@aschillinger20 - Use SUMMARIZE perhaps:

Measure = SUMX(SUMMARIZE('Table',[Production Order Number],"__Production Hours",MAX([Production Hours]),[__Production Hours])

[aschillinger20]

@Greg_Deckler 

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. 

[Greg_Deckler · Accepted solution]

@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]

@Greg_Deckler 

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"? 

[Greg_Deckler]

@aschillinger20 - Usually it is the other way around. Can you give me a better sample data set and I'll test?

[aschillinger20]

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.

[aschillinger20]

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? 

[aschillinger20]

@Greg_Deckler sent link to workbook in PM. 

[aschillinger20]

@amitchandak 

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? 

@Greg_Deckler 

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.

[amitchandak]

@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])

[aschillinger20]

@amitchandak 

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.