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

[vemee]

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

[123abc · Accepted solution]

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×(n−1) 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×(n−1) 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×(n−1) 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.

[vemee]

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!