Cumulative Distinct Count by Month AND within Reporting Category

[kellzp22]

Hi Everyone,

I'm trying to calculate the cumulative total of distinct clients, by month & program. HOWEVER, I would like to present this in a pie graph visual by Reporting Category. Below I have included an example of the data.

Using Jessica as an example, She attended 2 x Art programs in July - Counted as 1 distinct value. Additionally she attended a Music Program which would also be counted as 1 distinct value. Therefore, when reporting by 'Reporting Category' the value for Jessica would be 2. If Jessica had any August events, these events would be added on top of this total. I hope this make sense. Please let me know if you need any additional information. Thanks in advance for you help 🙂

[parry2k]

@kellzp22 try the following measure

Measure = SUMX ( SUMMARIZE ( Music, Music[Category],Music[Program], Music[Month], "_p", DISTINCTCOUNT( Music[Name] ) ), [_p] ) 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[EduSurveys]

@parry2k , @kellzp22  I had this exact same question, and this solution helped me as well. Thanks!

[kellzp22]

This works a treat thank you so much. Do you mind if I ask what the "_p" and [_p] do??? Sorry if it's a dumb question.. I'm intrigued.. 

[parry2k]

@kellzp22 no, it is ok, as much as you want. basically with summarize we are creating a table on fly and _p is just playing a role of column name which holds distinct count value.

hope it helps.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[kellzp22]

Terrific, that makes sense. Thanks again, greatly appreicate your help.