Add missing year(categorical column) and assign zero value to count for each medicine category

Question

My sample dataset as follows:YearMedicineCount2018M112019M112020M112018M212019M212018M31I need output as follows:YearMedicineCount2018M112019M112020M112018M212019M212020M202018M312019M302020M30&nbsp;I want to have all the values in the Year column for each medicine and assign value 0 to the counts for missing year. The year column will have more years moving forward - starting from 2018 and then every year, a new year category will be added. For example: It's 2022 now, so years in my dataset are from 2018 till 2022, but will have 2023, 2024.... every year.Any help would be highly appreciated! Thanks!

bcdobbs · Accepted Answer

Hi,&nbsp;I imported your data and called it Table1 (normally give it a descriptive name!).I then created two calculated tables:Medicine = DISTINCT ( Table1[Medicine] )Year = DISTINCT ( Table1[Year] )&nbsp;Then created relationships:&nbsp;Then in Table1 added a measure:# Medicines = SUM ( Table1[Count] ) + 0The +0 stops it returning blank.&nbsp;You can then use them in a table visual with Medicine and Year coming from your new dimension tables:&nbsp;&nbsp;

Forum Discussion

Related content

X Axis Year Order (Continuous vs Categorical)

Average of categories over vendor

Categorize a year column

RANK by category?

Grouping Measures into categories