Filtered sum

[AjithTravvise]

Hello,

      I have the below table and I wated to show the "country wise" sum of "total_test" in my report, but unfortunately its giving me a wrong value. If no filter applied the total value should appear.

 

[v-deddai1-msft]

Hi @AjithTravvise ,

Would you please use the following measure :

Test Completed =

CALCULATE (

    SUM ( 'owid-covid-data'[total_tests] ),

    FILTER (

        ALLSELECTED ( 'owid-covid-data' ),

        'owid-covid-data'[date] IN DISTINCT ( 'owid-covid-data'[date] )

    )

)

If it doesn't meet your requirement, Could you please inform us more detailed information, such as  your expected output.

Best Regards,

Dedmon Dai

[AjithTravvise]

The data is cummulative so its adding up alltogether, please try using "Country/ Region" filter and select "Australia" 

iso_code	Country/Region	date	total_cases	new_cases	total_deaths	new_deaths	total_cases_per_million	new_cases_per_million	total_deaths_per_million	new_deaths_per_million	total_tests	new_tests	total_tests_per_thousand	new_tests_per_thousand	tests_units
ARG	Argentina	4/7/2020	1628	74	53	7	36.021	1.637	1.173	0.155	null	null	null	null
ARG	Argentina	4/8/2020	1715	87	60	7	37.946	1.925	1.328	0.155	13330	null	0.295	null	tests performed
ARG	Argentina	4/9/2020	1795	80	65	5	39.716	1.77	1.438	0.111	14850	1520	0.329	0.034	tests performed
ARG	Argentina	4/10/2020	1894	99	79	14	41.907	2.19	1.748	0.31	16379	1529	0.362	0.034	tests performed
ARG	Argentina	4/11/2020	1975	81	82	3	43.699	1.792	1.814	0.066	18027	1648	0.399	0.036	tests performed
AUS	Australia	1/30/2020	6	2	0	0	0.235	0.078	0	0	54	null	0.002	null	units unclear
AUS	Australia	1/31/2020	7	1	0	0	0.275	0.039	0	0	71	17	0.003	0.001	units unclear
AUS	Australia	2/1/2020	9	2	0	0	0.353	0.078	0	0	90	19	0.004	0.001	units unclear
AUS	Australia	2/2/2020	11	2	0	0	0.431	0.078	0	0	105	15	0.004	0.001	units unclear

[v-deddai1-msft]

I have edit my original reply, Would you please check if it meets your requirements? And if it doesn't, please give more details about what you want.

[AjithTravvise]

I tried it but its giving me the total of all values, i need the sum. The valued in the column is cumulative so teh result is adding all the vaules.

[AjithTravvise]

The below measure worked, but the issue is certain countires the last date is still not updated, so for those i wanted to display the previous date number. 

 

[amitchandak]

@AjithTravvise ,Can you share sample data and sample output.

The formula above seems to compare it with the max date. So I doubt, what you are planning to get