KPI unichar based on average

[jochendecraene]

Hi

I've created a KPI with DAX measure using swith & unichar. I want the KPI to based on an average (by category). So the KPI has to change depending on this average by category. Since I can not use the average in a calculated column I needed to create some measures first that I can use in this formula:

Now, this means I have to create a lot of extra work, since I have lots of tables ... I've tried this solution, that seems to work. But I'm not sure that this is a good solution. Maybe there is a better way. Can someon give me some feedback or advice?

[v-xiaoyan-msft]

Hi @jochendecraene ,

Based on what you have mentioned above, I hope I have understood your needs correctly.
You want to create a KPI which is based on the average of the categories.
Show arrow when [Very Hard] column > average, otherwise blank.

If so, then Noeleke gave the right answer and you should create a calculated column instead of a Measure. You don't need to create a separate Measure for the average to do the calculation, you can do what he said and create a calculated column.

As for your 2nd problem, Sometimes when the results logic test are equal, I get an arrow, sometimes not.

In the calculation of your measure (average), it is automatically rounded, for example, in the second line of V12, although it shows 3%, the actual average is only 2.75%.

Hope it helps,

Community Support Team _ Caitlyn

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

[jochendecraene]

Thnx, this helped me!

[Anonymous · Accepted solution]

Hi @jochendecraene ,

You say that you cannot use the average in a calculated column. Why is that?

If you create a column with the following formula, you get the results you want I think.

Unichar =

Var _AverageByCat = CALCULATE( AVERAGE( 'Poverty'[MS Very Hard] ) , ALLEXCEPT('Poverty' ,'Poverty'[Category] ) )

Return

IF( Poverty[MS Very Hard] > _AverageByCat , UNICHAR(9650) , BLANK() )

[jochendecraene]

This gives me an error in the second part of the formula. Seems dax doesn't recongnize the column

If I adjust this part, It seems to work, and I don't need any measures, exept, there is somethin else wrong. Sometimes when the results logic test are equal, I get an arrow, sometimes not ...