How to work with avg function with alternate states

[LovelyKrishna]

Hi Team,

i would like to show the avg values for one of the indicator field, Can you please help me how to write the expression.

See the sample data below.

Load * Inline [
Year,Indicator,DataPoint,Country,Customer,Month
2019,Waste,450,India,A,Jan
2019,Score,15,India,B,Jan
2019,Waste,50,India,A,Feb
2019,Score,15,India,B,Feb
];

I will be setting up two states (State1 and State2) for the indicator field and i have taken the line chart with two dimension (Year, Country) and one metric (DataPoint) in this example.

Case1: When i select 'Waste' values for State1 Indicator field then i would like to show the Sum(DataPoint) and this case it would be 500 and we can show this into the report.

Case2: When i select 'Score' value for State2 Indicator field then i would like to show the Avg(DataPoint) and this case it would be 15 but i could see in the report 30.Therefore here i would require your help, How to show the Avg(DataPoint) value is 15 in the report.

Can you please help me how to achieve the Case2 issue, Thanks in advance.

Regards,

Krishna.

[Anil_Babu_Samineni]

When you say state1 and state2 are they alternate states? If not, Try this way

Pick(Match(Indicator, 'Waste', 'Score'), Sum(Score), Avg(Score), Sum(Score))

[LovelyKrishna]

Hi Anil,

Thanks for your help and suggestion.

I have got the solution and given expression is work out for my problem.

if(only({[Indicator1]} Indicator)='Score',Sum(Aggr(Avg({[Indicator1]}DataPoint),Year,Country)),sum({[Indicator1]}DataPoint))
/
if(only({[Indicator2]} Indicator)='Score',Sum(Aggr(Avg({[Indicator2]}DataPoint),Year,Country)),sum({[Indicator2]}DataPoint))