Forum Discussion
DAX function to count rows, Group by Column and divide by total count of rows
Dax function for the below steps from table AAA
- Count the number of rows in column “coldef” using group by condition. This column has data type as Text
- Count the total rows in column “coldef” which has data type as text
- Divide step 1 by step 2
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 TeamIf 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!
6 Replies
Anonymous , Based on what I got. Try a measure like
Divide( Countrows(Summarize(Table, Table[Column]) ), countrows(Table) )
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 TeamIf 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!
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% 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 TeamIf 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!
