Conditional column for distinct values of another column

[Anonymous]

Hi everyone,

I have a table like the one below. 

Customer	Quote	Hours	DesiredOutcome
Apple	123456	50	1
Apple	123465	50	0
Apple	123456	100	1
Microsoft	987654	200	1
Microsoft	987654	200	0
Microsoft	456321	50	1

I am trying to create the DesiredOutcome column so I can then create a measure that sums up all the hours of each distinct quote number. Is there a way to create a column like that or another way to get to the measure value I eventually want? I am not sure how to go about this exactly. The measure value would return 400 once everything was all working.

Any guidance would be super helpful! Thank you!

[Anonymous · Accepted solution]

Hi @Anonymous 

Due to the duplicate value in your table ,you need to create an index column to distinguish them .Add an index column in Query Editor and then return to Desktop view .

Then create a calculated column to return the next hour in turn .

hour 2 =
var _index='Table'[Index]+1
return
CALCULATE(SELECTEDVALUE('Table'[Hours]),FILTER(ALLEXCEPT('Table','Table'[Customer]),'Table'[Index]=_index))

Create a measure to compare the two hour columns .

desire result = SWITCH(TRUE(),SELECTEDVALUE('Table'[Hours])=SELECTEDVALUE('Table'[hour 2]) ,1,SELECTEDVALUE('Table'[hour 2])=0,1,0)

The final result is as shown :

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.