Need Distictcount of total

[Arvind_123]

Hi Team,

I have measures that giving me total of all projects disitctcount but I need overall distictcount to be show in total:

here are my measure and column to achive the sum of distinctcount of all sources:

Column I have created to extract New

1)

KeywordStatus =

IF (

    NOT (

        COUNTROWS (

            FILTER (

                ALL('BT3 Keyword (2)'),

                'BT3 Keyword (2)'[Keyword] = EARLIER('BT3 Keyword (2)'[Keyword]) &&

                'BT3 Keyword (2)'[Management Group] = EARLIER('BT3 Keyword (2)'[Management Group])&&

                'BT3 Keyword (2)'[Actual Source] = EARLIER('BT3 Keyword (2)'[Actual Source])&&

                'BT3 Keyword (2)'[Month Id] < EARLIER('BT3 Keyword (2)'[Month Id])

                            )

        )

    ),

    "New",

    "Common"

)

2)then I have created new measure to count distinct of "New" keyword from above column is

NewC =

CALCULATE(

    DISTINCTCOUNT('BT3 Keyword (2)'[Keyword]),

    FILTER(

        'BT3 Keyword (2)',

        'BT3 Keyword (2)'[KeywordStatus] = "New"

    )

)

3)after using this I am getting sum of all distictcount is total of all sources but I need overall distinctcount

to be show in total instead of shwoing sum of all disnctcount.
i have used measure for summing the total is like this:

NewDistictCount = var t=SUMMARIZE('BT3 Keyword (2)','BT3 Keyword (2)'[Actual Source],"SourceDistinct",[NewC] )

return

SUMX(t,[SourceDistinct])

but I want total should be display as overall distinct count not sum of all source.

I am getting table like this

and expected total should be distinct count overall.
Dec

2599
4357
26931
2517
13045
346
95
16
71908 -Expected Total distinct

Thanks,

Arvind

[Anonymous]

Hi, @Arvind_123 

According to your description, for the time being, I cannot understand what you mean by "sources", you can share the pbix file without sensitive data or post a complete reproduction of the problem with the data table, measure, desired effect, and the logic to achieve the effect and other information.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Arvind_123]

Hi @Anonymous ,

Source is like sub project.

[Arvind_123]

Hi Team,

more info for you guys,

instead of KeywordStatus calculated Column in above when I used this Calculated column I am getting total is correct distinctcount overall is correct but the Source level it differs:

KeywordStest =

IF (

    NOT (

        COUNTROWS (

            FILTER (

                ALL('BT3 Keyword (2)'),

                'BT3 Keyword (2)'[Keyword] = EARLIER('BT3 Keyword (2)'[Keyword]) &&

                'BT3 Keyword (2)'[Month Id] < EARLIER('BT3 Keyword (2)'[Month Id])

                            )

        )

    ),

    "New",

    "Common"

)