DAX Help - Calculation with Filters for KPI data

[KeepCalm007]

Hello Power BI community

I have a table of KPI data for a list of services.

Each service has different KPI light indicator ranges based on the group it is from. I was able to create a column to show KPI lights for each service's quarter 1 results based on their individual targets using the switch function - this is working fine.

The problem I have is with the overall group KPI. This is the calculation I am having difficulty with.

I have tried combinations of Switch and Calculate and Filter functions, but must be getting the order of things wrong, I have gotten the error message: "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

The KPI result for a group depends on the achievement of the individual services:

If it helps, this is a screen snip of the data in Power BI:

What I want to achieve is having a traffic light KPI show for each Group A-E, based on the results achieved by the individual services within that group.

Tips and advice appreciated regarding the DAX structure appreciated.

[v-danhe-msft]

Hi @KeepCalm007,

Based on my research, it seemed that you could use the "UNCHAIR" function to switch you data to different KPI symbol, you could refer to below link:

https://community.powerbi.com/t5/Community-Blog/Power-BI-Desktop-DAX-Using-Unicode-Arrows-as-Measure...

https://blog.crossjoin.co.uk/2017/04/11/the-dax-unichar-function-and-how-to-use-it-in-measures-for-d...

Regards,

Daniel He

[KeepCalm007]

Hi Daniel @v-danhe-msft

Thanks for your suggestion. However, it is not the output symbol I am having difficulty with, it is the calculation (formula) to get the right one for each 'group'.

In the examples you provided, the measure is based the same conditions for the whole length of the column whether an upward arrow or downward arrow is showing.

This is not the case for my situation 😞