Forum Discussion
Helper IPlotting a cumulative chart based on distinct counts
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.
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.
4 Replies
- amitchandak
Super User
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.
matthewtjySorry 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
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.