how to fetch a value by comparing multiple column values with in the same table

[Anonymous]

Hi all,

I have created a calulated table with columns Monthyear,status ,Module and completion % in PBI Desktop . I want to find out  Deviation % from the goal
by comparing 'module','Monthyear' and fetch respective complete% of 'status' complete  then, complete%- Module specific target(for aaa 85% , for bbb 90%) .

Require column data would look like as 'Final format'

Finally i am gonna show this data in matrix as shown below.

Hope that makes sense. Any help will be appreciated.

[v-diye-msft · Accepted solution]

Hi @Anonymous 

You can get the Deviation using below measure:

Deviation = var a  = CALCULATE(MAX([Completition%]),FILTER(ALL('Table'),[Status]="Complete"),VALUES('Table'[Module]),VALUES('Table'[MonthYear]))
Return
IF(MAX('Table'[Module])="aaa",a-0.85,a-0.9)

Then create the matrix:

pbix attached.

[Anonymous]

 Hi v-diye-msft ,

Thank you for the solution. Keep rock the same way.!!!

Cheers!