Total YTD on various levels

[hcze]

Hi,

I need some helps to calculate total YTD using DAX with some conditions:

- No date table. It works with TOTALYTD function but it is not what I am looking for.

- The formula should take care of filters in the query as well as from outside (e.g. slicer).

Expected output looks similar to the following screenshots

Sample data as follow:

Company	Category	Customer	Product	Year	Quarter	Value
AAA	Cat1	Amazon	Prod1	2018	1	10
AAA	Cat1	Amazon	Prod1	2018	1	2
AAA	Cat1	Amazon	Prod3	2018	1	12
AAA	Cat1	Amazon	Prod1	2018	2	23
AAA	Cat1	Amazon	Prod1	2018	2	24
AAA	Cat1	Amazon	Prod3	2018	2	12
AAA	Cat1	Amazon	Prod1	2018	3	4
AAA	Cat1	Amazon	Prod2	2018	3	45
AAA	Cat1	Amazon	Prod3	2018	3	5
AAA	Cat1	Amazon	Prod3	2018	4	3
AAA	Cat1	Google	Prod1	2018	1	23
AAA	Cat1	Google	Prod3	2018	1	37
AAA	Cat1	Google	Prod3	2018	1	26
AAA	Cat1	Google	Prod1	2018	2	12
AAA	Cat1	Google	Prod2	2018	2	34
AAA	Cat1	Google	Prod2	2018	2	78
AAA	Cat1	Google	Prod1	2018	3	34
AAA	Cat1	Google	Prod1	2018	3	60
AAA	Cat1	Google	Prod3	2018	3	10
AAA	Cat1	Google	Prod1	2018	4	34
AAA	Cat2	Amazon	Prod1	2019	1	67
AAA	Cat2	Amazon	Prod1	2019	1	13
AAA	Cat2	Amazon	Prod3	2019	1	12
AAA	Cat2	Amazon	Prod1	2019	2	18
AAA	Cat2	Amazon	Prod1	2019	2	18
AAA	Cat2	Amazon	Prod3	2019	2	2
AAA	Cat2	Amazon	Prod1	2019	3	23
AAA	Cat2	Amazon	Prod2	2019	3	41
AAA	Cat2	Amazon	Prod3	2019	3	45
AAA	Cat2	Amazon	Prod3	2019	4	37
AAA	Cat2	Google	Prod1	2019	1	40
AAA	Cat2	Google	Prod3	2019	1	8
AAA	Cat2	Google	Prod3	2019	1	55
AAA	Cat2	Google	Prod1	2019	2	48
AAA	Cat2	Google	Prod2	2019	2	23
AAA	Cat2	Google	Prod2	2019	2	18
AAA	Cat2	Google	Prod1	2019	3	10
AAA	Cat2	Google	Prod1	2019	3	43
AAA	Cat2	Google	Prod3	2019	3	9
AAA	Cat2	Google	Prod1	2019	4	75
BBB	Cat1	Amazon	Prod1	2018	1	6
BBB	Cat1	Amazon	Prod1	2018	1	45
BBB	Cat1	Amazon	Prod3	2018	1	36
BBB	Cat1	Amazon	Prod1	2018	2	9
BBB	Cat1	Amazon	Prod1	2018	2	67
BBB	Cat1	Amazon	Prod3	2018	2	21
BBB	Cat1	Amazon	Prod1	2018	3	15
BBB	Cat1	Amazon	Prod2	2018	3	38
BBB	Cat1	Amazon	Prod3	2018	3	26
BBB	Cat1	Amazon	Prod3	2018	4	24
BBB	Cat1	Google	Prod1	2018	1	35
BBB	Cat1	Google	Prod3	2018	1	14
BBB	Cat1	Google	Prod3	2018	1	37
BBB	Cat1	Google	Prod1	2018	2	36
BBB	Cat1	Google	Prod2	2018	2	24
BBB	Cat1	Google	Prod2	2018	2	19
BBB	Cat1	Google	Prod1	2018	3	21
BBB	Cat1	Google	Prod1	2018	3	28
BBB	Cat1	Google	Prod3	2018	3	43
BBB	Cat1	Google	Prod1	2018	4	27
BBB	Cat2	Amazon	Prod1	2019	1	16
BBB	Cat2	Amazon	Prod1	2019	1	36
BBB	Cat2	Amazon	Prod3	2019	1	18
BBB	Cat2	Amazon	Prod1	2019	2	23
BBB	Cat2	Amazon	Prod1	2019	2	27
BBB	Cat2	Amazon	Prod3	2019	2	31
BBB	Cat2	Amazon	Prod1	2019	3	40
BBB	Cat2	Amazon	Prod2	2019	3	15
BBB	Cat2	Amazon	Prod3	2019	3	21
BBB	Cat2	Amazon	Prod3	2019	4	34
BBB	Cat2	Google	Prod1	2019	1	10
BBB	Cat2	Google	Prod3	2019	1	21
BBB	Cat2	Google	Prod3	2019	1	34
BBB	Cat2	Google	Prod1	2019	2	16
BBB	Cat2	Google	Prod2	2019	2	11
BBB	Cat2	Google	Prod2	2019	2	10
BBB	Cat2	Google	Prod1	2019	3	4
BBB	Cat2	Google	Prod1	2019	3	23
BBB	Cat2	Google	Prod3	2019	3	35
BBB	Cat2	Google	Prod1	2019	4	24

Thank you,

Lin

[dax]

Hi hcze, 

According to your description, it seems that when the Category in Matrix, you don't want to show running total in it, right? If so, you could try to use below measure to see whether it work or not.

Measure 2 = if(ISINSCOPE('Table'[Category]),Sum('Table'[Value]),
VAR SelectedQuarter =
    MAX ( 'Table'[Quarter] )
RETURN
    CALCULATE (
        Sum('Table'[Value]),
        FILTER ( ALL ( 'Table'[Quarter] ), 'Table'[Quarter] <= SelectedQuarter )
    ))

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.

[hcze]

Thanks, @dax Zoe. I would prefer to make the DAX more flexible, meaning I could drop other dimensions (e.g. Product or Customer) and still get the correct running total.

Also FILTER ALL removes all filters and it is not the number I want.

[dax · Accepted solution]

Hi hcze， 

This should work, it is based on Quarter to calculate running total, so when [Quarter] field in Table or Matrix, it should calculate running total correctly. In addition, in your image, I saw you don't want to calculate running total when [Category] in visual, so I use if condition in measure. You could refer to my sample.

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.

[hcze]

That's perfect. Thanks Zoe and Happy New Year!

@dax 

[Tahreem24]

@hcze ,

Give a try using below query.

YTD Measure =
VAR SelectedQuarter =
    MAX ( Table[Quarter] )
RETURN
    CALCULATE (
        Sum(Table[TotalColumn]),
        FILTER ( ALL ( Table[Quarter] ), Table[Quarter] <= SelectedQuarter )
    )

Don't forget to hit THUMBS UP and mark it as a solution if it helps you!

[hcze]

Thanks Tahreem but the FILTER ALL removes all filters from inside and outside the matrix but I want the opposite.