cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Counting values in query and using counted value in equation?

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

1 ACCEPTED SOLUTION
Microsoft Employee

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Microsoft Employee

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors