topic Re: Sum of Distinct Count by column in Desktop
https://community.powerbi.com/t5/Desktop/Sum-of-Distinct-Count-by-column/m-p/68397#M28375
<P>So how would you filter out a distict value?</P><P> </P><P>So in the example above how would you say return the distinct values = SPRING15</P><P> </P>Wed, 14 Sep 2016 20:40:02 GMTwharding2016-09-14T20:40:02ZSum of Distinct Count by column
https://community.powerbi.com/t5/Desktop/Sum-of-Distinct-Count-by-column/m-p/28665#M9577
<P>Hello Everyone,</P><P> </P><P>I am having trouble calculating sum of Distinct Count. Basically, I would like to calculate Distinct count regarding a different column. Here is the example:</P><P> </P><P>Term ID</P><P>Spring 15 1</P><P>Spring 15 2</P><P>Spring 15 1</P><P>Spring 16 1</P><P>Spring 16 1</P><P>Spring 16 3</P><P>Spring 17 4</P><P>Spring 17 1</P><P> </P><P>My original formula is DISTINCTCOUNT(Table[ID]). But it gives a total number 4. However, I would like to calculate sum of distinct ID for every term. It should have,</P><P>-2 distinct count for Spring 15,</P><P>-2 distinct count for Spring 16</P><P>-2 distinct count for Spring 17.</P><P> </P><P>So distinct count would be 6 instead of 4.</P><P> </P><P>By the way, there is something to point out. I was eligible to get correct number with a complicated formula but my page should react when we filter with a slicer. When I have something like (="Spring 15"), it does not response when Spring 15 is unselected in slicer.</P><P> </P><P>It is not as complicated as it is seen. To recap, distinct count should be calculated for every data in column "term".</P><P> </P><P>Sorry for the long message. I hope I can solve my issue.</P>Mon, 11 Apr 2016 16:06:15 GMThttps://community.powerbi.com/t5/Desktop/Sum-of-Distinct-Count-by-column/m-p/28665#M9577burak2016-04-11T16:06:15ZRe: Sum of Distinct Count by column
https://community.powerbi.com/t5/Desktop/Sum-of-Distinct-Count-by-column/m-p/28668#M9580
<P>You could create a new column that concatenates Term and ID and then create a measure that does a DISTINCTCOUNT of that column. This measure should react appropriately to slicers and such. So:</P><P> </P><P>Column</P><PRE>TermIDs =CONCATENATE([Term],[ID])</PRE><P>Measure</P><PRE>DistinctTermIDs = DISTINCTCOUNT([TermIDs])</PRE>Mon, 11 Apr 2016 16:15:24 GMThttps://community.powerbi.com/t5/Desktop/Sum-of-Distinct-Count-by-column/m-p/28668#M9580Greg_Deckler2016-04-11T16:15:24ZRe: Sum of Distinct Count by column
https://community.powerbi.com/t5/Desktop/Sum-of-Distinct-Count-by-column/m-p/28669#M9581
<P>I am surprised how fast you replied.</P><P> </P><P>Thank you so much!</P>Mon, 11 Apr 2016 16:21:29 GMThttps://community.powerbi.com/t5/Desktop/Sum-of-Distinct-Count-by-column/m-p/28669#M9581burak2016-04-11T16:21:29ZRe: Sum of Distinct Count by column
https://community.powerbi.com/t5/Desktop/Sum-of-Distinct-Count-by-column/m-p/64489#M26630
<P>I have a set of ids </P><P> </P><P>1</P><P>2</P><P>3</P><P>1</P><P>1</P><P>2</P><P>4</P><P>5</P><P>5</P><P> </P><P>and i want the id and its count</P><P>1 - 3 times</P><P>2 - 2 times</P><P>3 - 1 time</P><P>4 - 1 time</P><P>5 - 2 times</P><P> </P><P>and i have written a measure for the same</P><P>Measure = CONCATENATE(DISTINCT(Activityx[Column1.AppId]), DISTINCTCOUNT(Activityx[Column1.AppId]))</P><P> </P><P> </P><P>but i am unable to get the expected result. How can i achive that?</P>Thu, 01 Sep 2016 09:27:43 GMThttps://community.powerbi.com/t5/Desktop/Sum-of-Distinct-Count-by-column/m-p/64489#M26630Adithya2016-09-01T09:27:43ZRe: Sum of Distinct Count by column
https://community.powerbi.com/t5/Desktop/Sum-of-Distinct-Count-by-column/m-p/64491#M26632
<P>I have a set of ids </P><P> </P><P>1</P><P>2</P><P>3</P><P>1</P><P>1</P><P>2</P><P>4</P><P>5</P><P>5</P><P> </P><P>and i want the id and its count</P><P>1 - 3 times</P><P>2 - 2 times</P><P>3 - 1 time</P><P>4 - 1 time</P><P>5 - 2 times</P><P> </P><P>and i have written a measure for the same</P><P>Measure = CONCATENATE(DISTINCT(Activityx[Column1.AppId]), DISTINCTCOUNT(Activityx[Column1.AppId]))</P><P> </P><P> </P><P>but i am unable to get the expected result. How can i achive that?</P>Thu, 01 Sep 2016 09:31:36 GMThttps://community.powerbi.com/t5/Desktop/Sum-of-Distinct-Count-by-column/m-p/64491#M26632Adithya2016-09-01T09:31:36ZRe: Sum of Distinct Count by column
https://community.powerbi.com/t5/Desktop/Sum-of-Distinct-Count-by-column/m-p/68397#M28375
<P>So how would you filter out a distict value?</P><P> </P><P>So in the example above how would you say return the distinct values = SPRING15</P><P> </P>Wed, 14 Sep 2016 20:40:02 GMThttps://community.powerbi.com/t5/Desktop/Sum-of-Distinct-Count-by-column/m-p/68397#M28375wharding2016-09-14T20:40:02ZRe: Sum of Distinct Count by column
https://community.powerbi.com/t5/Desktop/Sum-of-Distinct-Count-by-column/m-p/85856#M36022
<P>Thanks , this helped me.</P>Thu, 03 Nov 2016 23:41:48 GMThttps://community.powerbi.com/t5/Desktop/Sum-of-Distinct-Count-by-column/m-p/85856#M36022Buminda2016-11-03T23:41:48ZRe: Sum of Distinct Count by column
https://community.powerbi.com/t5/Desktop/Sum-of-Distinct-Count-by-column/m-p/97181#M40935
<P>Hello, </P><P>I would like to ask question as a continue of your answer, when we calculate a measure</P><PRE>DistinctTermIDs = DISTINCTCOUNT([TermIDs])</PRE><P>How to put this measure result in new column for every product (row). </P><P> </P><P>Example, we have as a product name</P><P>Spring15 </P><P><SPAN>Spring15 </SPAN></P><P><SPAN>Spring16</SPAN></P><P> </P><P><SPAN>And create </SPAN>DISTINCTCOUNT measure that we can manually filter and for Spring 15 Total will return 2.</P><P>How to put this value 2 in the same table, near each item?</P><P> </P><P>Result should be</P><P>ProductName No. (from measure)</P><P>Spring15 2</P><P>Spring15<SPAN> 2</SPAN></P><P>Spring16<SPAN> 1</SPAN></P><P> </P><P>Thank you in advance</P>Sat, 03 Dec 2016 01:15:30 GMThttps://community.powerbi.com/t5/Desktop/Sum-of-Distinct-Count-by-column/m-p/97181#M40935vedran2016-12-03T01:15:30Z