Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Distinct hours taken per quote

Hi everyone,

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

CustomerQuoteProductHoursDesiredOutcome
Apple44444PC1001
Apple44444Phone1000
Microsoft66666Xbox501
Microsoft55555Office501

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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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
Super User
Super User

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

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

@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

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.   

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

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

 

NewPlsHelp_1-1627060233147.png

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. 

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?

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.