I have created a table, values in it are only values which comes in all the years selected in slicer ex: if a participant id is present in all the selected year then only it will be displayed else not.
Now i want another table in which i can sow values on yearly basis for only which are present in the above table.
ex: i have 3 years selected in slicer, 15 participant ids are id's which are present in all the 3 years so suppose for 2015, i have 5000 sum ,2016 sum is 2000, 2017 sum is 7500.
so, how can i fetch respective values in this table.
I have tried creating table, but it is showing sum of all the values in that particular year irrespective of the filter(that an ID should be present in all the selected years).
The code for fetching id present in all the selected years is: Measure1 = VAR _EmptySlicer = CALCULATE ( ISFILTERED ( 'table'[CalcCensusYear] ), ALLSELECTED ( 'table'[CalcCensusYear] ) ) = FALSE() RETURN IF ( NOT ( _EmptySlicer ), IF ( CALCULATE ( DISTINCTCOUNT ( 'table'[CalcCensusYear] ), ALLSELECTED('table'[CalcCensusYear] ) = COUNTROWS ( ALLSELECTED ( 'table'[CalcCensusYear] )), 1 ), 1 )
2015 total sum 2000, participant ids 20 2016 total sum 3000 30 2017 total sum 5000 50
suppose slicer selection have 3 years (2015,2016,2017) ids present in all 3 years are 10 .sum is 6000 for individual year sum is 1000,1500,2000
current result: for 2015 total average : 2000(total sum for 2015)/30(count of common participant ids for 3 years)
expected result: 1000(sum of 2015 selected ids)/ 10(count of common ids)