Calculate dynamic percentage from 2 fields in a table that will work in visuals when filtering

[dajackson]

Hi all,

I have a calculated column that is meant to show the percentage performance against the target based on two columns: target and actual. I have hundreds of rows for each country and I want to be able to use the performance column to use the total target and actual for each country (and for other fields). What formula should I be using as presently it looks like this:

Performance = Divide((vPSTFlatReport[Actual]),(vPSTFlatReport[Target]))

And this is a matrix visualisation showing the result:

Country	Target	Actual	Average of Performance
Ethiopia	25,076,482	10,201,268	7%
Kenya	7,510,619	1,487,022	118%
Malawi	62,017	94,231	294%
Mozambique	380,912	313,548	166%
Republic of Sudan	1,259,200	295,116	6%
Tanzania	807,768	679,097	240%
Uganda	442,556	411,350	83%
Zambia	3,751,037	1,505,904	93%
Zimbabwe	2,744,798	5	13%

I have a number of rows where the actual is blank, what I want the performance column to show is the actuals total for any filtered data set divided by the target for that smae filtered dataset. The example above shows this for country but I have other fields that I would like this table to work for. 

Thank you

[TeigeGao]

Hi @dajackson ,

Actually, in your scenario, you just need to use the CALCULATE(Divide((vPSTFlatReport[Actual]),(vPSTFlatReport[Target]))) to get the result, it will consider all filters on the dataset.

Best Regards,

Teige

[dajackson]

@TeigeGao 

Thank you very much for the suggestion.

This formula returns a #ERROR and gives me the warning:

"A single value for column 'Actual' in table 'vPSTFlatReport' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

Best

D