Forum Discussion
Return Accumulate distinct count and make multiple selection slicer work on the same page
I am trying to accumulate distinct count of many numbers and use a slicer to control the chart.
Below is the data structure and two results I hope to get.
| Customer_ID | Event_Name | Order Status | Weeks_Before_Due | Gender | Uniqle ID |
| A001 | Paris, 2016 | Done | 0 | Female | 2016_A001 |
| A002 | Paris, 2016 | Done | 0 | Male | 2016_A002 |
| A003 | Paris, 2016 | Done | 1 | Female | 2016_A003 |
| A004 | Paris, 2016 | Done | 2 | Male | 2016_A004 |
| A007 | Paris, 2016 | Cancel | 2 | Male | 2016_A007 |
| A008 | Paris, 2016 | Done | 3 | Female | 2016_A008 |
| A010 | Paris, 2016 | Done | 5 | Female | 2016_A010 |
| A002 | Chicago, 2017 | Done | 0 | Male | 2017_A002 |
| A004 | Chicago, 2017 | Done | 0 | Male | 2017_A004 |
| A005 | Chicago, 2017 | Done | 0 | Female | 2017_A005 |
| A006 | Chicago, 2017 | Done | 2 | Female | 2017_A006 |
| A007 | Chicago, 2017 | Done | 3 | Male | 2017_A007 |
| A008 | Chicago, 2017 | Done | 6 | Female | 2017_A008 |
| A001 | Tokyo, 2018 | Done | 0 | Female | 2018_A001 |
| A003 | Tokyo, 2018 | Done | 2 | Female | 2018_A003 |
| A004 | Tokyo, 2018 | Cancel | 3 | Male | 2018_A004 |
| A006 | Tokyo, 2018 | Done | 3 | Female | 2018_A006 |
| A007 | Tokyo, 2018 | Done | 3 | Male | 2018_A007 |
| A008 | Tokyo, 2018 | Done | 4 | Female | 2018_A008 |
| A009 | Tokyo, 2018 | Done | 5 | Male | 2018_A009 |
| A010 | Tokyo, 2018 | Done | 5 | Female | 2018_A010 |
There are three events and several cutomers attended those events, a customer can attend more than one event.
Attendees can start to pay for the tickets six weeks before the event start, so zero means they pay on site when event begin.
Cancel records should be excluded.
I started by creating some unique IDs and created a seperate table with ALL(EVENT_NAME,WEEKS_BEFORE_DUE) and CALCULATE(DISTINCTCOUNT(CUSTOMER_ID). It works when users only select one option in slicer (scenario 1).
Yet, when users need to choose more than one, an error happened. Also, I don't know how I can get accumulate values for this data structure.
Can someone kindly help me realize the second and thrid scenarios?
Many thanks!
Hi, Anonymous
Based on your description, I created data to reproduce your scenario.
You may create a measure as follows and display the result with 'Clustered column chart'. Please check the results without 'Cancel' status.
Count = CALCULATE( DISTINCTCOUNT('Table'[Uniqle ID]), 'Table'[Order Status]<>"Cancel" )Result:
s1:
s2:
s3:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- amitchandak
Super User
I did not get it completely. We can cumulative using date like this
Cumm distinct count= CALCULATE(distinctcount(Sales[Sales Date]),filter(date,date[date] <=maxx(date,date[date])))
- Greg_Deckler
Community Champion
Sample data as text please. 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/38490
v-alq-msftCommunity Support
Hi, Anonymous
Based on your description, I created data to reproduce your scenario.
You may create a measure as follows and display the result with 'Clustered column chart'. Please check the results without 'Cancel' status.
Count = CALCULATE( DISTINCTCOUNT('Table'[Uniqle ID]), 'Table'[Order Status]<>"Cancel" )Result:
s1:
s2:
s3:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- v-alq-msft
Hi, Anonymous
If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.
Best Regards
Allan
