Forum Discussion

ryan0521's avatar
ryan0521
Frequent Visitor
6 years ago

DISTINCT COUNT SUM

Hello everyone,

 

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

 

ClassOperating days
1304
2304
3290
4221
52
643
7123
843
total304

 

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
Need Help

3 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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,