Distinct Count selected elements in a column

Question

Hi,&nbsp;I'm struggling with a calculated column.&nbsp;I'd like to make a line diagram to compare 2017 - 2019 revenue trends with 2020, with the possibility to filter between 2017 - 2019.&nbsp;So if you have all years selected, the sum of the 3 years revenue is diveded by 3, if you select two years then by 2 and if you only click on one year then by 1.&nbsp;I wrote a measure which seems to work perfectly but when I copy it in to the calculated column it doesn't work at all.&nbsp;It basically says: "If 2020 is part of the filter than deduct one, if it is not then count the years up to 3 (there are more years but I'm only showing 2017, 2018, 2019)&nbsp;IF(IF(CONTAINS(FinancialData,FinancialData[Date].[Year],2020) = TRUE ,DISTINCTCOUNT(FinancialData[Date].[Year]) - 1, DISTINCTCOUNT(FinancialData[Date].[Year]))&gt;=3 , 3 ,IF(CONTAINS(FinancialData,FinancialData[Date].[Year],2020) = TRUE ,DISTINCTCOUNT(FinancialData[Date].[Year]) - 1, DISTINCTCOUNT(FinancialData[Date].[Year]))))&nbsp;Can someone help me out how to make it work in a calculated column?&nbsp;BR,Oliver

amitchandak · Answer

OliverMatsik , You can create two slicers one on depending on the date table and another on an independent date.&nbsp;
From the date table, you can take distinct count of year and divide.
&nbsp;
Something like this. You have use year in place of dates
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
&nbsp;

greg_deckler · Answer

OliverMatsik&nbsp;Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490The most important parts are:1. Sample data as text, use the table tool in the editing bar2. Expected output from sample data3. Explanation in words of how to get from 1. to 2.

anonymous · Answer

Hi&nbsp;OliverMatsik&nbsp;,&nbsp;See if this is what you are looking at&nbsp;https://www.youtube.com/watch?v=hGdYR1KR3rchttps://www.youtube.com/watch?v=MhYEqniwunghttps://www.youtube.com/watch?v=dVxg_ZO1gn0&nbsp;Regards,Harsh Nathani

Forum Discussion

Distinct Count selected elements in a column

3 Replies

Related content

count distinct

Distinct Count

Bookmark Navigator - Assigning distinctive Colours to different buttons upon selection

Show rows having common elements

display multiple elements with SELECTEDVALUE