Distinct hours taken per quote

[Anonymous]

Hi everyone,

I have a table similar to the one below where I am trying to add up all the hours each quote took.

Customer	Quote	Product	Hours	DesiredOutcome
Apple	44444	PC	100	1
Apple	44444	Phone	100	0
Microsoft	66666	Xbox	50	1
Microsoft	55555	Office	50	1

I want to total the hours taken for each quote rather than total all of the hours if a 2 or more different products are listed on the same quote. The answer I am looking for is 200 hours rather than 300 hours because Apple has one quote "44444" and that quote took 100 hours. I don't think the DesiredOutcome column would work for this scenario, but I am unsure of what else to do.

If anyone has any advice that would be great!

[Anonymous · Accepted solution]

I was able to get the value I wanted using the formula below

Total Hours = VAR MyTempTable =
    ADDCOLUMNS(
        SUMMARIZE('FY2021 Quote Status', 'FY2021 Quote Status'[ Quote],'FY2021 Quote Status'[Hours Num]),
        "DistinctValue", CALCULATE(MAX('FY2021 Quote Status'[Hours Num]))
    )
    return 
    SUMX( MyTempTable, [DistinctValue])

[vanessafvg]

what is the purpose of desired income? is it used to say which is valid?

[Anonymous]

@vanessafvg 

That column doesn't exist. I was trying to make a column like that so I could make a measure that adds up all the hours of each row that has "1" in it. I thought that could be one way to do it, but I don't think that would be possible. If that makes sense

[vanessafvg]

can you give an example  of how you will use this data?

i think what im confused about it how will you know whether to sum the hours  of a quote, do you just use the first row of that quote.   

[Anonymous]

@vanessafvg 

I am trying to calculate the total sum of hours all the quotes took. The issue with the data is one quote number could be associated with 5 products. For example

Quote Number       Product       Hours

123                              PC              50 

123                              PC              50

123                              PC              50

123                              PC              50

123                              PC              50

The way the data is if I sum all of the hours up it will say it took 250 hours for this quote. When it reality it took 50 hours because all of the products are on the same quote. 50 hours is the answer I am trying to get in this scenario if the data was filter to quote number 123.

This quote says it took 15 hours because of the different product associated with it. But, if i take the the sum of hours it says 30. I want to have a one-to-one relationship with the quote number and the hours num taken. That is where I am stuck.

As to how I would use it, say I wanted to see all the distinct quotes requested in June 2021. It would tell me the total hours.

I hope this makes sense. 

[vanessafvg]

Hi @Anonymous 

ok not quite sure how the data is structured in your model but from what you saying you are saying there is more than one line per quote due to there being a variation of products, but the total hours on each line is a duplication?  is that correct?

so to get the total hours for the quote it would just be taking the min value from the table as the ie.  min(table[hours]).

how are you expecting this to look in a visual?