I am new to DAX and using Power Query & Powerpivot in Excel 2016 to transform and load data to a pivot. I am simply trying to get a list of Unique Characteristics Values (CHAR VAL) and their counts (Sub-Totals) shown for each year. This measure should adjust itself when i add different filters like Country, Char Description etc to Pivot.
| Item Appearance Date | Item Appearance Date (Year) | CHAR DESCRIPTION | CHAR VALUE |
| 2017-02-07 | 2018 | LSC | BBBB |
| 2017-04-19 | 2017 | LSC | CCCC |
| 2017-04-19 | 2017 | LCC | CCCC |
| 2017-04-19 | 2017 | CCM | BBBB |
| 2017-04-19 | 2017 | CLCM | BBBB |
| 2017-06-01 | 2017 | LCC | CCCC |
| 2017-06-07 | 2017 | LSC | AAAA |
| 2017-06-07 | 2017 | CCM | DDDD |
| 2017-07-04 | 2018 | CCM | GGGG |
| 2017-07-05 | 2018 | CLCM | AAAA |
| 2017-07-07 | 2018 | LCC | WWWW |
| 2017-07-07 | 2018 | AVMC | AAAA |
| 2016-07-17 | 2016 | CLCM | EEEE |
| 2016-07-17 | 2016 | LSC | FFFF |
| 2016-10-11 | 2016 | CLCM | EEEE |
| 2016-10-12 | 2016 | AVMC | ZZZZ |
| 2017-10-15 | 2017 | LSC | AAAA |
| 2017-10-18 | 2017 | LCC | HHHH |
| 2017-10-18 | 2017 | CCM | BBBB |
| 2018-03-11 | 2017 | CCM | BBBB |
| 2018-03-13 | 2017 | LCC | YYYY |
| 2018-03-13 | 2017 | CLCM | AAAA |
| 2018-04-12 | 2018 | CLCM | DDDD |
| 2018-04-13 | 2018 | CCM | DDDD |
| 2018-04-17 | 2018 | LCC | CCCC |
| 2018-04-17 | 2018 | AVMC | CCCC |
| 2018-06-01 | 2018 | LSC | RRRR |
| 2018-06-01 | 2017 | LCC | WWWW |
| 2018-06-01 | 2017 | CCM | XXXX |
| 2018-06-05 | 2017 | CLCM | AAAA |
| 2018-06-05 | 2017 | AVMC | CCCC |
| 2018-06-12 | 2017 | AVMC | BBBB |
| 2018-06-12 | 2017 | LSC | HHHH |
I am trying to answer the following questions:
- how many Unique Char Values are present for each Char Description each year?
- what are the top 5 or 10 Char Values for each Char Description each year?
- If we have 2 years of data, can we compare past year vs previous to determine Emerging or Trending values?
- Anything else interesting about this data?
| Count of CHAR VAL | CHAR DESC | | | | |
| Item Appearance Date (Year) | CHAR VAL | AVMC | CCM | CLCM | LCC | LSC | UniqueCharCounts |
| 2018 | DDDD | | 1 | 1 | | | |
| | BBBB | | | | | 1 | |
| | GGGG | | 1 | | | | 1 |
| | WWWW | | | | 1 | | |
| | RRRR | | | | | 1 | 1 |
| | AAAA | 1 | | 1 | | | |
| | CCCC | 1 | | | 1 | | |
| 2018 Total | | 2 | 2 | 2 | 2 | 2 | 2 |
| 2017 | DDDD | | 1 | | | | |
| | HHHH | | | | 1 | 1 | 1 |
| | AAAA | | | 2 | | 2 | |
| | WWWW | | | | 1 | | |
| | YYYY | | | | 1 | | 1 |
| | XXXX | | 1 | | | | 1 |
| | BBBB | 1 | 3 | 1 | | | |
| | CCCC | 1 | | | 2 | 1 | |
| 2017 Total | | 2 | 5 | 3 | 5 | 4 | 3 |
| 2016 | FFFF | | | | | 1 | 1 |
| | EEEE | | | 2 | | | 1 |
| | ZZZZ | 1 | | | | | 1 |
| 2016 Total | | 1 | | 2 | | 1 | 3 |
I have tried following formulae, but not getting the unique list of items and their unique counts in pivot for each year. e.g. like this:
DistinctCounts:=DISTINCTCOUNT('Table1'[CHAR VAL]))
UniqueCharCount:=CALCULATE(COUNTROWS(DISTINCT('Table1'[CHAR VAL])),'Table1'[Item Appearance Date (Year)])
Can any DAX Experts please help me quickly?
