New Column to Count Distinct a text field partitioned on another field

[vamsikb]

Following is my current table with the first 2 columns (Group By and To Count):

Group by	To Count	New Column
A	XYZ	2
A	XYZ	2
A	MNO	2
B	MNO	3
B	GHK	3
B	TRP	3

I'm trying to add the new column which partitions the table by "Group by" column and finds distinct count of "To Count" column. This new column needs to be used for another calculation, hence it should be static value across the table. What's the best way to achieve this?

[Ashish_Mathur]

Hi,

Write this calculated column formula

=CALCULATE(DISTINCTCOUNT(Data[To Count]),FILTER(Data,Data[Group By]=EARIER(Data[Group By])))

Hope this helps.

[amitchandak · Accepted solution]

Allexcept is used to get the partitioned by

Refer this ia a good example

https://community.powerbi.com/t5/Desktop/highest-value-by-category/td-p/428758

Try

New Column =
    CALCULATE (
        count ( 'Table'[To Count] ),
        ALLEXCEPT ( 'Table', 'User Table'[Group by] )
    )

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin