Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 | Prod1 | 2018 | 1 | 23 | |
AAA | Cat1 | Prod3 | 2018 | 1 | 37 | |
AAA | Cat1 | Prod3 | 2018 | 1 | 26 | |
AAA | Cat1 | Prod1 | 2018 | 2 | 12 | |
AAA | Cat1 | Prod2 | 2018 | 2 | 34 | |
AAA | Cat1 | Prod2 | 2018 | 2 | 78 | |
AAA | Cat1 | Prod1 | 2018 | 3 | 34 | |
AAA | Cat1 | Prod1 | 2018 | 3 | 60 | |
AAA | Cat1 | Prod3 | 2018 | 3 | 10 | |
AAA | Cat1 | 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 | Prod1 | 2019 | 1 | 40 | |
AAA | Cat2 | Prod3 | 2019 | 1 | 8 | |
AAA | Cat2 | Prod3 | 2019 | 1 | 55 | |
AAA | Cat2 | Prod1 | 2019 | 2 | 48 | |
AAA | Cat2 | Prod2 | 2019 | 2 | 23 | |
AAA | Cat2 | Prod2 | 2019 | 2 | 18 | |
AAA | Cat2 | Prod1 | 2019 | 3 | 10 | |
AAA | Cat2 | Prod1 | 2019 | 3 | 43 | |
AAA | Cat2 | Prod3 | 2019 | 3 | 9 | |
AAA | Cat2 | 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 | Prod1 | 2018 | 1 | 35 | |
BBB | Cat1 | Prod3 | 2018 | 1 | 14 | |
BBB | Cat1 | Prod3 | 2018 | 1 | 37 | |
BBB | Cat1 | Prod1 | 2018 | 2 | 36 | |
BBB | Cat1 | Prod2 | 2018 | 2 | 24 | |
BBB | Cat1 | Prod2 | 2018 | 2 | 19 | |
BBB | Cat1 | Prod1 | 2018 | 3 | 21 | |
BBB | Cat1 | Prod1 | 2018 | 3 | 28 | |
BBB | Cat1 | Prod3 | 2018 | 3 | 43 | |
BBB | Cat1 | 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 | Prod1 | 2019 | 1 | 10 | |
BBB | Cat2 | Prod3 | 2019 | 1 | 21 | |
BBB | Cat2 | Prod3 | 2019 | 1 | 34 | |
BBB | Cat2 | Prod1 | 2019 | 2 | 16 | |
BBB | Cat2 | Prod2 | 2019 | 2 | 11 | |
BBB | Cat2 | Prod2 | 2019 | 2 | 10 | |
BBB | Cat2 | Prod1 | 2019 | 3 | 4 | |
BBB | Cat2 | Prod1 | 2019 | 3 | 23 | |
BBB | Cat2 | Prod3 | 2019 | 3 | 35 | |
BBB | Cat2 | Prod1 | 2019 | 4 | 24 |
Thank you,
Lin
Solved! Go to 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.
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.
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.
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 ,
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!
Thanks Tahreem but the FILTER ALL removes all filters from inside and outside the matrix but I want the opposite.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |