DAX function to count rows, Group by Column and divide by total count of rows

[Anonymous]

Dax function for the below steps from table AAA

1. Count the number of rows in column “coldef” using group by condition. This column has data type as Text
2. Count the total rows in column  “coldef” which has data type as text
3. Divide step 1 by step 2

[Anonymous]

Thanks much for helping on this. Let me elaborate my question clearly. 

Source Table
Column - A (TEXT)	Column - B (TEXT)
Furniture	A01234
Furniture	A12345
Accessories	C24564
Accessories	C22335
Accessories	D57845
Accessories	D65894
Accessories	E55882
Equipment	K98562
Equipment	K85857
Equipment	K22335
Equipment	L65875
Equipment	L78548
Equipment	L12548

Required Output
Column - A (TEXT)	Count of Column - B	% DAX
Furniture	2	15%
Accessories	5	38%
Equipment	6	46%
Total	13	100%

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try:

Count of Column = COUNTROWS('Table')

- B% DAX = 
VAR allCount = CALCULATE(COUNTROWS('Table'),ALL('Table'))
RETURN
DIVIDE([Count of Column],allCount)

Once created, if you want to display percentages without decimals, you can check "%" in the Measure tool tab and then select the number of decimals to be followed by zero.

If you use the Format function, it will default to two decimal places.

- B% DAX = 
VAR allCount = CALCULATE(COUNTROWS('Table'),ALL('Table'))
RETURN
FORMAT(DIVIDE([Count of Column],allCount), "Percent")

Drag the created measures to the report page for display, the page effect is shown below:

The pbix file is attached.

If you have any other questions please feel free to contact me.

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!

[Anonymous]

Thanks a lot for your help. 

[Anonymous]

Thank you very much, Amit for your help. 

This still needs some modification as the expected result should be something as below:

Row 1 - 55/277

Row 2 - 40/277 ..........

Column 1	Row Count (Grouped)	Divide
A	55	20%
B	40	14%
C	20	7%
D	78	28%
E	6	2%
F	78	28%
	277	100%

[Anonymous]

Hi @Anonymous ,

This is sample data:

This is your expected result:

Please try:

TotalColumn1 = CALCULATE(SUM('Table'[Row Count (Grouped)]),ALL('Table'))

Column = DIVIDE([Row Count (Grouped)],[TotalColumn1])

You can do it with a measure, but it defaults to two decimals when converted to a percentage, and total is wrong, so it is better to use a New column.

The pbix file is attached.

If you have any other questions please feel free to contact me.

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!

[amitchandak]

@Anonymous , Based on what I got. Try a measure like

Divide( Countrows(Summarize(Table, Table[Column]) ), countrows(Table) )