KPI from survey responses - transform data to make it suitable for KPI visual

[Cristian]

Hi All,

I would like to ask if you can recommend a way to manipulate the data below in Power BI so it is suitable for a KPI visual:

Date	Answer
01-Jan-18	Agree
01-Jan-18	Agree
01-Jan-18	Disagree
01-Jan-18	Neutral
01-Jan-18	null
01-Feb-18	Agree
01-Feb-18	Agree
01-Feb-18	Agree
01-Feb-18	Disagree
01-Feb-18	Neutral

I would like to know how the answers changed month by month. I.e. in February 60% "Agreed" (Indicator) vs January when only 40% "Agreed" (Goal).

Many thanks,
Cristian

[v-danhe-msft · Accepted solution]

Hi @Cristian,

Based on my test, you could refer to below steps:

Create below measures:

Goal = CALCULATE(COUNT(Table1[Answer]),FILTER(ALL(Table1),'Table1'[Answer]="Agree"&&MONTH('Table1'[Date])=1))/CALCULATE(COUNT(Table1[Date]),FILTER(ALL(Table1),'Table1'[Date]=MAX('Table1'[Date])))

Indicator = CALCULATE(COUNT(Table1[Answer]),FILTER(ALL(Table1),'Table1'[Answer]="Agree"&&MONTH('Table1'[Date])=2))/CALCULATE(COUNT(Table1[Date]),FILTER(ALL(Table1),'Table1'[Date]=MAX('Table1'[Date])))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[Cristian]

Hi @v-danhe-msft,

Many thanks for the reply. This is the sort of thing I'm after.

Cheers,
Cristian