Counting values in query and using counted value in equation?

[hanr]

Hi, I'm new to PowerBI, I was wondering what the best way to count values in a group then use the count in a calculation, I have a query like this:

0 1    2    3

a  .     .     T

a  .     .     T

a  .     .     F

b  .     .     T

b  .     .     null

b  .     .     F

b  .     .     F

I understand that if you create a Table from this query, it will group by a and b and you can create counts of T or F, where T and F are strings with null values, within the table, but would you use the counts in a dependent calcuation? So that the table looked like:

0  1   2   TF_calc

a   .   .    T_count^2/F_count^2 

b   .   .    T_count^2/F_count^2 

Thanks in advance!

[v-danhe-msft · Accepted solution]

Hi @hanr ,

Based on my test, you could refer to below formula:

Test = SUMMARIZE('Table1','Table1'[0],"A",
        (CALCULATE(COUNT(Table1[3]),FILTER('Table1','Table1'[3]="T"))*CALCULATE(COUNT(Table1[3]),FILTER('Table1','Table1'[3]="T")))
        /(CALCULATE(COUNT(Table1[3]),FILTER('Table1','Table1'[3]="F"))*CALCULATE(COUNT(Table1[3]),FILTER('Table1','Table1'[3]="F"))))

Result:

You could also downlad the pbix file to have a view.

Regards,

Daniel He