DISTINCT COUNT SUM

[ryan0521]

Hello everyone,

I have a problem with the total of my DIstinctcount formula as shown below;

Class	Operating days
1	304
2	304
3	290
4	221
5	2
6	43
7	123
8	43
total	304

My formula in operating days is;

Operating days = CALCULATE(DISTINCTCOUNT('KSA BK'[Date].[Date]),FILTER('KSA BK',[2019 Sales]<>BLANK()))

However, I want to sum up all those operating days and put them as a total.

Thank you in advance

[v-lid-msft]

Hi @ryan0521 ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[v-lid-msft]

Hi @ryan0521 ,

We can create a measure use following formula to meet your requirement:

Operating days = 
SUMX (
    ADDCOLUMNS (
        DISTINCT ( 'KSA BK'[Class] ),
        "Days", CALCULATE (
            DISTINCTCOUNT ( 'KSA BK'[Date] ),
            FILTER (
                'KSA BK',
                [Class] = EARLIER ( 'KSA BK'[Class] )
                    && [2019 Sales] <> BLANK ()
            )
        )
    ),
    [Days]
)

BTW, pbix as attached.

Best regards,

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

[Peejz_Jalmasco]

@ryan0521 

Do you have a sample file?