Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
vemee
Regular Visitor

Help calculating number of occurrences of all values, and then summing the value of all occurrences

Hi,

I would really appriciate any help on how to write a measure to first count the occurance of all differente values in a column, and then sum the occurance of each different value togeter.

Country:

USA

Finland
Denmark
Finland
USA

USA

 

For column above I would like the measure to first calculate the occurance of each value (USA = 3, Finland = 2, Denmark = 1), and then calculate the sum of n*(n-1) (where n = 3, 2, 1 (in example above))


Hence, the formula should calculate n*(n-1) for all values obtained for calculating the occurance of each contry (indepentedent on how many conutries that are in the list).

Is this possible? Any help would be highly appriciated.

Best, Ville

1 ACCEPTED SOLUTION
123abc
Community Champion
Community Champion

To achieve this in Power BI, you can create a measure using DAX (Data Analysis Expressions). Here's how you can create a measure to calculate the sum of �×(�−1)n×(n1) for the occurrences of each value in the "Country" column:

 

Occurrence_Sum =
VAR CountryOccurrences =
SUMMARIZE(
'YourTableName', -- Replace 'YourTableName' with the name of your table
'YourTableName'[Country],
"Occurrence", COUNTROWS('YourTableName')
)
RETURN
SUMX(
CountryOccurrences,
[Occurrence] * ([Occurrence] - 1)
)

 

Replace 'YourTableName' with the name of your table where the "Country" column resides.

This measure first calculates the occurrences of each country using the SUMMARIZE function and stores them in the CountryOccurrences variable. Then, it uses the SUMX function to iterate over each row in CountryOccurrences, calculating �×(�−1)n×(n1) for each occurrence count, and finally sums up the results to give you the desired total.

After creating this measure, you can add it to your report and it should give you the sum of �×(�−1)n×(n1) for the occurrences of each country in the "Country" column.

 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

 

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

View solution in original post

2 REPLIES 2
123abc
Community Champion
Community Champion

To achieve this in Power BI, you can create a measure using DAX (Data Analysis Expressions). Here's how you can create a measure to calculate the sum of �×(�−1)n×(n1) for the occurrences of each value in the "Country" column:

 

Occurrence_Sum =
VAR CountryOccurrences =
SUMMARIZE(
'YourTableName', -- Replace 'YourTableName' with the name of your table
'YourTableName'[Country],
"Occurrence", COUNTROWS('YourTableName')
)
RETURN
SUMX(
CountryOccurrences,
[Occurrence] * ([Occurrence] - 1)
)

 

Replace 'YourTableName' with the name of your table where the "Country" column resides.

This measure first calculates the occurrences of each country using the SUMMARIZE function and stores them in the CountryOccurrences variable. Then, it uses the SUMX function to iterate over each row in CountryOccurrences, calculating �×(�−1)n×(n1) for each occurrence count, and finally sums up the results to give you the desired total.

After creating this measure, you can add it to your report and it should give you the sum of �×(�−1)n×(n1) for the occurrences of each country in the "Country" column.

 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

 

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Hi,

Thank you!! It works perfectly 🙂 One more question, is there any possiblity to not include the blank values (null) when performing the calculation above. As it is right now it will also think that "null" is a country, and add this to the final sum?

Will mark your previous reply as an accepted solution!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.