How to divide cost by count of duplicate order numbers

[cheid_4838]

I have a dashboard that contains repair orders and the estimated cost for the entire repair.  Some of the repairs have more than one line. The estimated cost shown in the screenshot needs to be split between each line.  I tried dividing the cost up using max section sequence and counting the ordernumber or PO Number.  When I create a measure I have to sum the estimated cost so I get the original value when I divide by the section sequence # or counting the order#/PO#.  Is there anyway to divide the estimated cost by the stop sequence number or PO# count without having to sum the estimated cost?  Thanks in advance for your help.

[DataNinja777]

Hi @cheid_4838 ,

What about using max instead of sum to aggregate Estimate value?  

Average Estimate by Max Section Sequence = 
VAR MaxSectionSequence = 
    CALCULATE(
        MAX('Table'[Section Sequence]), 
        ALLEXCEPT('Table', 'Table'[PO Number])
    )
VAR TotalEstimate =
    CALCULATE(
        max('Table'[Estimate]), 
        ALLEXCEPT('Table', 'Table'[PO Number])
    )
RETURN
    TotalEstimate / MaxSectionSequence

The output is as shown below:

I have attached a sample pbix file for your reference.

Best regards,

[cheid_4838]

That did work, but now it only shows the average when I remove the stop sequence number.  How do I get it to sum to the original amount when the stop sequence number is removed and have the total line to show the original total versus the line item amount?

With Stop Sequence

Without Stop Sequence

[DataNinja777 · Accepted solution]

Hi @cheid_4838 ,

How about updating the measure as shown below?

Average Estimate by Max Section Sequence (update) = 
VAR MaxSectionSequence = 
    CALCULATE(
        MAX('Table'[Section Sequence]), 
        ALLEXCEPT('Table', 'Table'[PO Number])
    )
VAR TotalEstimate =
    CALCULATE(
        max('Table'[Estimate]), 
        ALLEXCEPT('Table', 'Table'[PO Number])
    )
RETURN
   sumx('Table', TotalEstimate / MaxSectionSequence)

The output is shown below:

I have attached a sample pbix file.

[cheid_4838]

Thanks for your help.  I learn something all the time from this site.  

[sroy_16]

Hello @cheid_4838 

Try using these measure for the solution.
Create a new measure to calculate the total estimated cost for each repair order:
TotalEstimatedCost = SUM(TableName[EstimatedCost])

Create a measure to count the number of lines (or section sequences) for each repair order:

LineCount = COUNTROWS(
FILTER(
TableName,
TableName[RepairOrderID] = MAX(TableName[RepairOrderID])
)
)

Create a measure to calculate the estimated cost per line:

CostPerLine = DIVIDE(
[TotalEstimatedCost],
[LineCount],
0 
)

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos.

[cheid_4838]

Unfortnately that didn't work because it summed the estimated cost (below) to get a total of $8,681.12.  Then divided that total by the line count to get the original amount of $4,340.56. I want to take $4,340.56 and  divide it between the number of lines per order.  I need to some how get around having to sum the estimated cost.