Plotting a cumulative chart based on distinct counts

[matthewtjy]

How do I plot a cumulative distinct count chart in PBI (like a running summation of distinct counts)?

I was able to find lots of information on cumulative summation charts however for my problem, I want to do it based on distinct count.

Example:

-There are 5 workplaces (A,B,C,D,E) that submits reports to me.

-So for example from beginning of time to 31 Dec 2014, I have only received reports from workplace E.

-Next, from beginning of time to 31 Dec 2015, I have received reports from workplaces B, E, G.

-I do not want to count how many reports were submitted to me, but rather how many distinct count of workplaces had submitted reports to me. So example in 2014, I would have 1 workplace; in 2015, I would have 3 workplaces.

-In the example data, column A is the workplace, column B is the date each report was submitted to me. So example in 2014, workplace A had submitted 6 reports. However, I would only want to count it once (because distinct count of workplace).

I apologise as I am totally new to Power BI but my boss wants me to self-learn.

Sorry, not sure how to upload a photo for quick reference.

Excel Workbook Example File 

[amitchandak]

@matthewtjy , do you have a date or you can use the year in place of the date. You can try measure like

Cumm Sales = CALCULATE(distinctcount('Table'[workplaces ]),filter(allselected('Table'),'Table'[Date] <=max('Table'[Date])))

Yet to check your excel.

[matthewtjy]

Sorry what do you mean by do I have a date? I would like to plot a chart based on the cumulative distinct count as at the end of each year e.g. end 2014, end 2015, end 2016, ..., all in 1 chart.

Sorry I'm not sure how else to explain it more simply, I'd post a screenshot of my excel file as it is easier to explain but I'm not sure how to upload a screenshot.

Thanks for replying though!

[amitchandak · Accepted solution]

@matthewtjy , Have year column in your table or use one from the Date hierarchy and try measure like

New column

Year = year([SUBMIT_DT])

New measure

Cumm Sales = CALCULATE(distinctcount('Table'[WORKPLACE_NO]),filter(allselected('Table'),'Table'[SUBMIT_DT] <=max('Table'[SUBMIT_DT])))

New measure with Date table, having year column

Cumm Sales = CALCULATE(distinctcount('Table'[WORKPLACE_NO]),filter(allselected('Date'),'Date'[Date] <=max('Date'[Date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[matthewtjy]

thanks!