Conditional Column on Live Connection

Question

Hello all I am using a live connection(Direct Query Mode)

I have a Measure I,e

Skill Index = DIVIDE(AVERAGE('SA USER_RATING'[Final_User_Rating]),

AVERAGE('SA USER_RATING'[MANAGER_EXPECTATIONS])

,0)
Now I need a Stacked Bar to show who are Critical and Not Critical. Critical is Skill Index <=0.5.
I tried creating a Calculated Column But I am getting an error saying On Direct Mode I can't use Average.
Please help me what is the other way to achieve this.
I need to filter some table based on stacked bar.I,e Critical / Not Critical.

User	Final User Rating	Manager Expectation	Skill Index
ABC	2	3	0.666666667
BCD	4	2	2
EFG	1	3	0.333333333

amitchandak · Answer

harshagraj , Try like
This should work as a measure
DIVIDE(sum('SA USER_RATING'[Final_User_Rating]),sum('SA USER_RATING'[MANAGER_EXPECTATIONS]))
or
new measure =
AVERAGEX('SA USER_RATING', DIVIDE('SA USER_RATING'[Final_User_Rating],'SA USER_RATING'[MANAGER_EXPECTATIONS]))
&nbsp;

harshagraj · Answer

Hi&nbsp;amitchandak&nbsp;thank you. But I cannot use measure in Legend. I need to show critical and not critical.

icey · Answer

Hi&nbsp;harshagraj&nbsp;,
&nbsp;
Try this:
&nbsp;
1. Enter data to create a Critical table.

&nbsp;
2. Create a measure.
Measure =
VAR Critical_ =
    IF ( [Skill Index] &lt;= 0.5, "Critical", "Not Critical" )
RETURN
    IF ( MAX ( Critical[Column1] ) = Critical_, [Skill Index] )

&nbsp;
3. Create a Stacked column chart.

&nbsp;
&nbsp;
Best Regards,
Icey
&nbsp;
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

harshagraj · Answer

Hi&nbsp;Icey&nbsp;thank you for the reply! It is working but I need to achieve this like in the below image.The above one i did it in Extract mode. There i could able to achieve in Column.Below are the formulas i have used.[Measure] User &amp; Comp LOD = CALCULATE([Skill Index],ALLEXCEPT('SA USER_RATING','SA USER_RATING'[COMPETENCY_L1_NAME],'SA USER_RATING'[USER_DN]))[Measure] Skill Flag_ = var critical_=CALCULATE(IF([User &amp; Comp LOD]&gt;=0 &amp;&amp; 'SA USER_RATING'[User &amp; Comp LOD]&lt;=0.4,"Far",IF([User &amp; Comp LOD]&gt;0.4 &amp;&amp; [User &amp; Comp LOD]&lt;=0.7,"Below",IF([User &amp; Comp LOD]&gt;0.7 &amp;&amp; [User &amp; Comp LOD]&lt;1,"Achievers",IF([User &amp; Comp LOD]&gt;=1,"Star")))),ALLEXCEPT('SA USER_RATING','SA USER_RATING'[COMPETENCY_L1_NAME],'SA USER_RATING'[USER_DN]))ReturnIF(MAX('Table'[Column1])=critical_,'SA USER_RATING'[User &amp; Comp LOD])LOD to be applied on Competency L1 and User_DN.&nbsp;