cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User
Super User

Re: Display unique items that appeared each year

@sifar786

 

Did you see the attached file in previous post

 

Both Formulas are working in my PC.

I am using your sample file

 

dip.png

 

 

Super User
Super User

Re: Display unique items that appeared each year

@sifar786

 

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]
    )
)
sifar786 Member
Member

Re: Display unique items that appeared each year

@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?

 

sample1.PNG

 

 

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?

 

 

sifar786 Member
Member

Re: Display unique items that appeared each year

@Zubair_Muhammad i checked the measures with my original data and works fine but Sub-Totals are not showing up, so i cannot Chart this data as Chart shows all 1's.

 

Also, this works only if i add Year, Char Desc & Char Val to Rows section in Pivot. Country and Category filters are not present in measure.

 

Need your urgent help.

sifar786 Member
Member

Re: Display unique items that appeared each year

@Zubair_Muhammad i checked the measures with my original data and works fine but SubTotals are not showing up, so i cannot Chart this data as Chart shows all 1's.

 

Also, this works only if i add Year, Char Desc & Char Val to Rows section in Pivot. Country and Category filters are not present in measure.

 

Need your urgent help.

 

Super User
Super User

Re: Display unique items that appeared each year

@sifar786

 

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]
    )
)

 

Community Support Team
Community Support Team

Re: Display unique items that appeared each year

Hi @sifar786,

 

Could you please mark the proper answers as solutions?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.