Cumulative totals in Stacked column chart over time by OS group

[Anonymous]

Hi Everyone,

I have been asked to create a few stacked column charts showing the cumulative total number of devices by their operating system group and over time (One chart spanning several years and another spanning over the months of a given year).

Sample data for the table is below:

OS (Groups) - which I manually created using the new group function in BI

DeviceCount - which I manually created with each record having a value of one. I thought it might be easier to use SUM on this Column instead of Count on rows (I am a n00b to Power BI so I don't know any better. 🤔)

I created the following new measure:

This works for some of the date periods but not all - it looks like it does not bring accross the cumulated data if the Operating System does not have a single installation date in the given period.

These are the two graphs I am working on.

You can see the Windows 8 systems dissappear in 2019 and the Windows 7 systems dissapear in 2020 even though we did have some devices with these OS at the given time.

It is even more obvious in this chart when looking at it Month to Month.

Any help would be greatly appreciated. Thanks in advance.

[Anonymous]

Hi @dax 

Its worked. Thanks for your assistance.

I am adding the measures for quick reference for anyone else who is interested.

Measure for Year by Year  Cumulative

Measure 6 = CALCULATE(SUM(table[count]), FILTER(ALLEXCEPT(table,table[os]), YEAR(table[date])<=MIN('calendar'[Date].[Year])))

Measure for Month by Month Cumulative

Measure 9 = CALCULATE(SUM(table[count]), FILTER(ALLSELECTED('calendar'[Date]),'calendar'[Date]<=MIN('calendar'[Date])))

[dax]

Hi @Anonymous,

Thank you for your sharing!

Best Regards,
Zoe Zhi

[Anonymous]

Hi @dax 

I just figured out why the icon was missing. The Auto date/time option was disabled for my pbix file. I will try to recreate your Calendar table and let you know my progress.

[Pragati11]

Hi,

Can you try this without creating any measure? Just drag the relevant fields to staked chart visual. Options are shown in screenshot attached.

Thanks.

[Anonymous]

Hi @Pragati11 

Sorry but it didnt work. It sums the totals for the given year/month but not all of the previous periods.

[dax · Accepted solution]

Hi @Anonymous , 

You could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @dax 

Your pbix looks to be exactly what I wanted.

However I ran into an issue when trying to recreate the measure. On my dashboard, it does not like the filter  - specifically  'calendar'[Date].[Year]

I believe it has to do with the way I manually created my Calendar table. In the Fields section, I dont seem to have the Calendar icon next to the Date column. I tried to recreate your Calendar table but with no success. How have you managed to create the Date Hierarchy without having any Year, Quarter, Month columns in your table?

I think if I can create the Calendar table and Date Hierarchy the same as yours, my original issue will be resolved.

Once again, thanks in advance.

[Pragati11]

Hi, @Anonymous 

I tried using a sample data as you mentioned in your query. It works for me. Can you attach your sample data please which is showing you issues?

Thanks.