Forum Discussion

yharfush's avatar
yharfush
Frequent Visitor
7 years ago
Solved

Measures over groups

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:   VEHIC...
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    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

MFelix's avatar
MFelix
Super User
7 years ago

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's avatar
yharfush
Frequent Visitor
7 years ago

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 :)