Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Display unique items that appeared each year

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 (...
Need Help
Show and Tell
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    Anonymous

     

    Try this revision

     

    Distinct Count With Totals =
IF (
    HASONEFILTER ( tblData[Item Appearance Date (Year) 1] )
        && HASONEFILTER ( tblData[CHAR DESCRIPTION] )
        && HASONEFILTER ( [CHAR VALUE] ),
    [DistinctCount],
    SUMX (
        SUMMARIZE (
            tblData,
            [Item Appearance Date (Year) 1],
            [CHAR DESCRIPTION],
            [CHAR VALUE]
        ),
        [DistinctCount]
    )
)

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Zubair_Muhammad Awesome! :manhappy:

 

I replaced the COUNT with COUNTA and that worked!

 

Question: Will these measures calculate automatically if i add other filters e.g. like (Countries or Categories) or move filters (e.g. Char Desc) from Rows to Columns area in Pivot?

 

The only problem i see is that except the Measure 1, the DistinctCount & DistinctCountWithTotals measures donot appear in Sub-Totals. See below Screenshot:

 

 

Also, if you can explain in brief how the 2 formulae work, it will be a good start to my learning curve.

 

Thanks.

 

Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
7 years ago

Anonymous

 

To get the Subtotals i.e for each year

 

use this formula

 

Distinct Count With Totals =
IF (
    HASONEFILTER ( tblData[Item Appearance Date (Year) 1] )
        && HASONEFILTER ( tblData[CHAR DESCRIPTION] ),
    [DistinctCount],
    SUMX (
        SUMMARIZE (
            tblData,
            [Item Appearance Date (Year) 1],
            [CHAR DESCRIPTION],
            [CHAR VALUE]
        ),
        [DistinctCount]
    )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Zubair_Muhammad This gives sub-totals now for only the Year column, and not for the Char Desc or Char Value columns. I think if i add more criteria in the Rows or Columns area of Pivot, then Sub-Totals will not appear for those new criteria, isn't it?

     

     

     

    Can this Totals measure be made more Generic so that it calculates unique values and their subtotals and totals correctly, when data is sliced/diced?