Measures over groups

[yharfush]

Hello,

I have a table with data regarding penetration of different media vehicles by city. I already have the first 4 columns shown below, and I want to calculate the last 3 columns:

VEHICLE	CITY	PENETRATION	TOTAL_PEOPLE	%PENETRATION	NATIONAL_PENETRATION	AFFINITY
A	1	120	253	47%	46%	103%
A	2	82	231	35%	46%	77%
A	3	71	190	37%	46%	81%
A	4	94	122	77%	46%	167%
B	1	80	111	72%	60%	121%
B	2	400	414	97%	60%	162%
B	3	113	350	32%	60%	54%
B	4	76	245	31%	60%	52%
C	1	350	372	94%	80%	117%
C	2	234	315	74%	80%	92%
C	3	123	168	73%	80%	91%
C	4	345	454	76%	80%	95%

The logic is as follows:

%PENETRATION = PENETRATION / TOTAL_PEOPLE (this is the penetration by city)

NATIONAL_PENETRATION = SUM(PENETRATION) / SUM(TOTAL_PEOPLE) for each vehicle (which is why the value gets repeated if we're refering to the same vehicle)

AFFINITY = %PENETRATION / NATIONAL_PENETRATION (it compares the penetration of one vehicle in one city, to the national penetration of that same vehicle)

I was able to calculate %PENETRATION and NATIONAL_PENETRATION using Measures, however when I try to calculate AFFINITY I'm not getting the result I want because I'm doing the average of NATIONAL_PENETRATION of all vehicles, and I don't know how to tell Power BI to do an average for each vehicle A, B and C. This is the formula I'm using:

AFFINITY =
DIVIDE(
[% PENETRATION],
AVERAGE('Table Name'[NATIONAL_PENETRATION])
)

I would appreciate any help or suggestions.

Best regards!

[MFelix · Accepted solution]

Hi @yharfush,

Create the following measures:

% Penetration = SUM(Affinaties[PENETRATION])/SUM(Affinaties[TOTAL_PEOPLE])

National penetration = CALCULATE( [% Penetration]; ALLEXCEPT(Affinaties;Affinaties[VEHICLE]))

affinity = [% Penetration]/Affinaties[National penetration]

This give the result below:

Regards,

MFelix

[yharfush]

Thank you so much, @MFelix! It works perfectly with the example I provided.

However, I would like to ask you if there is a way to integrate a condition into the ALLEXCEPT part of the formula. In my real data, I have a binary column that I'm using to filter the rows of my table, this is to indicate if that record belongs to a market or not. Because of this, the numbers I get for NATIONAL_PENETRATION are incorrect, I assume it is summing all values instead of only the ones that belong to the market. Do you know if this is possible?

Thank you so much 🙂