Excel pivot table with running total grand total is incorrect

Question

I have data with list of people and their activities by dates. I need to distinct count how many exact activities by one person in one day was made. My data:

Date	Person	Activity	Exact Activity
2023/04/04	Alex	Walking	Walking with the dog
2023/04/17	Jack	Hiking	Hiking in the mountains
2023/04/17	Jack	Hiking	Hiking in the mountains
2023/04/18	Alex	Hiking	Hiking in the mountains
2023/04/18	Alex	Hiking	Hiking in the mountains
2023/04/18	Alex	Hiking	Hiking in the mountains
2023/04/18	Alex	Walking	Walking with the dog
2023/04/19	Alex	Hiking	Hiking in the mountains
2023/04/20	Jack	Running	Running without gear
2023/04/26	Peter	Walking	Walking with the dog
2023/04/26	Peter	Hiking	Hiking in the mountains
2023/04/26	Agnes	Running	Running without gear
2023/04/26	Alex	Hiking	Hiking in the mountains
2023/04/27	Sandra	Hiking	Hiking in the mountains

So the result should be:

2023-04-04  Alex    1
2023-04-17  Jack    1
2023-04-18  Alex    2
2023-04-19  Alex    1
2023-04-20  Jack    1
2023-04-26  Peter   2
2023-04-26  Agnes   1
2023-04-26  Alex    1
2023-04-27  Sandra  1
Total               11

The desired result is two pivot tables like this:

| Distinct Count of Exact Activity | Column Labels |

Row Labels	Hiking	Running	Walking	Grand Total
2023-04-04			1	1
2023-04-17	1			1
2023-04-18	1		1	2
2023-04-19	1			1
2023-04-20		1		1
2023-04-26	2	1	1	3
2023-04-27	1
Grand Total	6	2	3	11

| Distinct Count of Exact Activity | Column Labels |

Row Labels	Agnes	Alex	Jack	Peter	Sandra	Grand Total
2023-04-04		1				1
2023-04-17			1			1
2023-04-18		2				2
2023-04-19		1				1
2023-04-20			1			1
2023-04-26	1	1		2		4
2023-04-27					1	1
Grand Total	1	5	2	2	1	11

I've created one pivot table where columns is activity, rows - date and person, values - distinct count of exact activity. And the result is:

| Distinct Count of Exact Activity | Column Labels |

Row Labels	Hiking	Running	Walking	Grand Total
2023-04-04			1	1
2023-04-17	1			1
2023-04-18	1		1	2
2023-04-19	1			1
2023-04-20		1		1
2023-04-26	1	1	1	3
Agnes		1		1
Alex	1			1
Peter	1		1	2
2023-04-27	1
Grand Total	1	1	1	3

I want that it would show total of unique count of exact activities by day, by person and grand total.

So I've tried in values use distinct count of exact activity and show values as running total in date, the result:

| Distinct Count of Exact Activity | Column Labels |

Row Labels	Hiking	Running	Walking	Totals
2023-04-04	0	1	0	1
2023-04-17	1	1	0	2
2023-04-18	2	2	0	4
2023-04-19	3	2	0	5
2023-04-20	3	2	1	6
2023-04-26	4	3	2	9
2023-04-27	5	3	2	10
Grand Total

It almost what I want despite that I also want to see grand totals as I showed in the desired results. I've found, that I need to create a measure and use it as value, but I don't know what DAX formula should I use to achieve this.

Another problem is that running total is incorrect by date, because it counts unique exact activities as well as unique activities on that date, so, on 2023-04-26 day instead of 4 it shows 3.

The same problem is with another pivot table where columns is person, rows - date, values - distinct count of exact activity and show values as running total in date:

| Distinct Count of Exact Activity | Column Labels |

Row Labels	Agnes	Alex	Jack	Peter	Sandra	Grand Total
2023-04-04	0	1	0	0		1
2023-04-17	0	1	1	0		2
2023-04-18	0	3	1	0		4
2023-04-19	0	4	1	0		5
2023-04-20	0	4	2	0		6
2023-04-26	1	5	2	2		9
2023-04-27	1	5	2	2	1	10
Grand Total

It shows the wrong totals, because if you would sum all the person values, the answer is 11, not 10.

How could I solve these problems? Maybe there is another way to calculate what I need not using pivot tables or maybe I just need to create the right measures?

Thanks in advance!

Answer 1

Overriding the default aggregation for the row, column and grand total areas requires a rather convoluted measure:

=
VAR T1 =
    SUMMARIZE (
        Table1, Table1[Date],
        "Distinct Count Exact Activity", DISTINCTCOUNT ( Table1[Exact Activity] )
    )
VAR T2 =
    SUMMARIZE (
        Table1, Table1[Person],
        "Distinct Count Exact Activity", DISTINCTCOUNT ( Table1[Exact Activity] )
    )
VAR T3 =
    SUMMARIZE (
        Table1, Table1[Date], Table1[Person],
        "Distinct Count Exact Activity", DISTINCTCOUNT ( Table1[Exact Activity] )
    )
VAR DateFiltered = ISFILTERED ( Table1[Date] )
VAR PersonFiltered = ISFILTERED ( Table1[Person] )
RETURN
    IF (
        DateFiltered && PersonFiltered,
        DISTINCTCOUNT ( Table1[Exact Activity] ),
        IF (
            PersonFiltered,
            SUMX ( T1, [Distinct Count of Exact Activity] ),
            IF (
                DateFiltered,
                SUMX ( T2, [Distinct Count of Exact Activity] ),
                SUMX ( T3, [Distinct Count of Exact Activity] )
            )
        )
    )