User-selected variable as a measure

[lisabarelle]

I would like to provide the choice to my -end-users to slice the data as they see fit. 

I've created a variable (vDimension) and a dropdown where users can select [sales] or [items]. I would then like to compute either the sum or the count. I've tried just with 'sum' but it doesn't work:

=sum({[RAG]='Green'}[vDimension])

Expected outcome would be:

vDimension= [sales] >>  =sum({[RAG]='Green'}[sales])

vDimension= [items] >>  =count({[RAG]='Green'}[items])

Any idea? I could of course write an if() statement in each KPI or graph, but is there an easier way?

[chrismarlow]

Hi,

Not tested, but does your variable not need to be inside $(), so;

=sum({[RAG]='Green'}[$(vDimension)])

Cheers,

Chris.

[Digvijay_Singh · Accepted solution]

I think single measure can work like this but you have to make decision through comparison to make a choice between sum and count - 

pick(match('$(vDimension)','sales','items'),

 sum({[RAG]='Green'}$(vDimension)),

count({[RAG]='Green'} $(vDimension))

)

You might need to format it further if your measure field has got more than single word to have '[' an ']'

Thanks,