Count against to other column

[Anonymous]

Hi All,

New to Power BI, i have data Category and Number.

I need the count as below, how can i write dax function. Please help me.

Category	Number	Count
AAA	11	2
BBB	11	2
CCC	22	4
DDD	22	4
EEE	22	4
FFF	22	4
GGG	33	3
HHH	33	3
III	33	3
JJJ	44	1

Thanks.

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Number", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Number"}, {{"GroupTables", each _, type table [Category=nullable text, Number=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "CountRows", each Table.RowCount([GroupTables])),
    #"Expanded GroupTables" = Table.ExpandTableColumn(#"Added Custom", "GroupTables", {"Category"}, {"Category"})
in
    #"Expanded GroupTables"

Hope this helps.

[bcdobbs · Accepted solution]

I would create a measure using the following (Replace Table1 with your table name):

Count of Category = 
    CALCULATE (
        COUNTROWS ( Table1 ),
        REMOVEFILTERS ( Table1[Category] )
    )

You can then drop the two columns and the measure into a table visual:

[Anonymous]

Thanks for your quick reply. I have created the measusre 

Count FMS1 =

CALCULATE( COUNTROWS ( Query1), REMOVEFILTERS (Query1[FMS_REPORTING_CATEGORY]))

It is showing as 1. but i need 4 instead of 1.

[bcdobbs]

What other columns are in your visual?