Forum Discussion

jochendecraene's avatar
jochendecraene
Helper V
4 years ago
Solved

KPI unichar based on average

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?

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    4 years ago

    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() )

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      jochendecraene
      Helper V
      4 years ago

      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 ...

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.