1

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!

Katia
  • 53
  • 13

2 Answers2

2

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] )
            )
        )
    )
Jos Woolley
  • 8,564
  • 2
  • 4
  • 9
  • Oh, wow! Thank you, works like a charm! One more question: when use date filter and choose this month (all dates are on April) on that pivot table, I'm getting the other grand total measure value (instead of 11, I get 8). What data gets filtered out? Is it possible to use filters on pivot with that measure and get correct results? – Katia Apr 28 '23 at 07:36
  • Thanks ones again, @JosWoolley, now I'm using measure only on data previously filtered out by date, but is it possible to filter pivot data and use this measure?? – Katia Jun 19 '23 at 06:35
1

So try using functions like unique() and countif() or countifs() like so:

enter image description here

Column E combines the name and activity, then unique takes that to single entries.

Column I gets the unique dates and column K has two drop downs to choose the date and the person plus activity.

You can expand that as needed.

Solar Mike
  • 7,156
  • 4
  • 17
  • 32
  • Thank you, but I will have lots of different data, so this solution wouldn't be very convenient to use. – Katia Apr 28 '23 at 07:40
  • @Katia I have used this on my quiz results where I have several thousand rows for some 150 students. Anyway each to their own. – Solar Mike Apr 28 '23 at 07:43