Calculated column using value from Measure

[Chan]

Hi

Team,

Need your help!

I have a Matrix report on Power BI , where I need to add a column which compares the total of the row against the value of dynamic measure and derived the outcome.

For Example, Total Score of each of the employee needs to match against the required score (which is a dynamic measure based on the slicer value) and calculate whether each employee has full access or partial access.

Current Output.

Desired Output:

Thanks for your help in advance!!

[MFelix · Accepted solution]

Hi @Chan ,

Not really sure how you have your data setup but I'm assuming is simlar to this:

Employee  Name Attribute Value

1	A	V1	2
1	A	V2	2
1	A	V3	2
1	A	V4	2
2	B	V1	2
2	B	V2	2
2	B	V3	1
3	C	V1	1
3	C	V2	2

If this is the case then you need to add a measure that looks like this:

Conditional column =
IF (
    CALCULATE ( SUM ( Table1[Value] ); ALLEXCEPT ( Table1; Table1[Employee] ) ) >= 'Required Score'[Required Score Value];
    "Full";
    "Part"
)

If your table has one column per each V you should do somehting like this:

Conditional column =
IF (
    CALCULATE (
        SUM ( Table1[V1] ) + SUM ( Table1[V2] )
            + SUM ( Table1[V3] )
            + SUM ( Table1[V4] );
        ALLEXCEPT ( Table1; Table1[Employee] )
    ) >= 'Required Score'[Required Score Value];
    "Full";
    "Part"
)

Be aware that if you are using  a matrix visual in order to use this column you need to make some "tricks", 

Add the measure to your values this will make a repeated value for each column you just need to resize all of the columns with conditional except one.

If you want to conditional format that column create the following measure:

Formatting = IF([Conditional column] = "full"; 1; 0)

And then use it's result to conditional formatting, check the image and file attach.

Top table is matrix wiht hidden column, bottom is table in original format.

Regards,

MFelix

[Chan]

Great!!

It works fine now.

Thanks for your support  MFelix .