topic Re: letter count in DAX Commands and Tips

letter count

JDU — Fri, 13 Sep 2019 13:58:46 GMT

Dear experts, I can't solve the letter count and then calculate their respective percentages of the total:

example:
Column 1
a
a
b
c
c
c

I need through DAX to be able to count to get the following result:
a: 2
b:1
c:3

and later:
a: 33%
b:17%
c: 50%

please your help!

Re: letter count

MatejZukovic — Fri, 13 Sep 2019 14:12:43 GMT

Hi JDU ,

try this to get count:

count = CALCULATE(COUNTA('Table'[Column 1]),ALLEXCEPT('Table','Table'[Column 1]))

and to get %:

% = 'Table'[count] / COUNTROWS('Table')

You can also combine two formulas into 1.

Regards,

Matej

Re: letter count

JDU — Fri, 13 Sep 2019 15:17:18 GMT

First of all thank you very much for your reply MatejZukovic

The first equation worked very well, the formula responds well, but the second equation does not, it brings me as a result 100% for each of the characters to count.

Where do you think the mistake might be?

Thanks again!

MatejZukovic wrote:
Hi JDU ,

try this to get count:
count = CALCULATE(COUNTA('Table'[Column 1]),ALLEXCEPT('Table','Table'[Column 1]))
and to get %:
% = 'Table'[count] / COUNTROWS('Table')
You can also combine two formulas into 1.
Regards,
Matej

Re: letter count

MatejZukovic — Mon, 16 Sep 2019 08:44:49 GMT

Hi JDU ,

previous approach would work when you create column in your backend table.

If you'd like to do the calculation as a measure, try this:

% = [count]/CALCULATE(COUNTA('Table'[Column 1]), ALL('Table'[Column 1]))