Running total with two categories (one of them is dynamical)

[Elvi_well]

Hi!

I’m trying to count running total of marks by studyyear and subject. When I count total it works.

Filter and legend don’t work. As example in the picture below Mark B never follow Year 2 and 3, mark C does not appear Year 2, total amount becomes wrong.

The same for subjects, Math1 is not shown year 2 and 3.

When I choose math1 in the filter, year2 and 3 disappear at all.

If I expect that mark A for math1 counts year 1,2,3, how could I change my DAX?
Thanks!

[Anonymous · Accepted solution]

Thanks for the reply from FreemanZ.

Hi @Elvi_well ,

Some categories are not displayed because the corresponding data does not exist for that year, so you can fill in the missing data for the year and use the column as a legend.

I create simple test data:

Please try the following steps:
1.Fill in the missing "mark" in each year.

2.Then create the combination of year and course name as shown here.

3.In Power Query Editor, create a merged table:

4.Create a new measure:

Measure2 = CALCULATE(COUNT('4Merge'[Ismark]),'4Merge'[Year]<=MAX('4Merge'[Year]))

5.Create visuals:

Best Regards,
Zhu

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

[Elvi_well]

Hello!
thanks a lot for your answer!
I just wanted to confirm one thing. I use a playground for this question, in reality I have a big dataset with many years, so if I would solve the problem with backend measures it could impact the size of the dataset/redundance if I need a special table just for this. Is it absolutly impossible to solve with help of DAX, something to remove filtercontext? or if there are no corresponding data other years DAX can't help in principle? 

[FreemanZ]

hi @Elvi_well ,

could you paste the corresponding data table?

[Elvi_well]

It is simulated, just for the problems description.