formula that picks up just the values of the city, not the entire wider region?

[Talos]

I have two tables such as below:

What I need to do is to have a map (as the visual) that would have each city with a darker/lighter colour (depending on percentage). This is based on: dividing the total number of males from within a City, from the 'AllClients' table to the same city's male population from the 'TotalPopulation' table.

So far, since I am new with Power Bi, I have 2 pages (one for male, one for female), with this measure for the male page:

MalePercentageOfTotPopInCity = var Male =calculate(sum('AllClients'[VallueAllocatedIs1]),FILTER('AllClients','AllClients'[Sex]="Male")) var AllSameSexPop = calculate(sum('AllSexPopulation'[TotalPopulation]),FILTER('AllSexPopulation', 'AllSexPopulation'[Sex]="Male")) return divide (Male, AllSameSexPop,blank())

The problem is that this formula, for the second part - var AllSameSexPop - it just adds up all the males in the country, while I would like it to recognise that there should be a different value, depending on which city the males are from, and so all male clients from Birmingham to be divided to all male population of Birmingham and London males with London male population, and so on...

Ideally, what I try to do is to get a formula that would allow me to have a filter with 'Male', 'Female' along the map (which is the visual) which would allow ticking one of these 2 options and the map's percentages to update accordingly. But if that would be too complicated, how can I fix the current, above formula? (do I need to create a calculated column first? Any suggestion is appreciated)

[MNedix · Accepted solution]

Hi,

I don't know how your data model looks like but you have to have some relationship between the two tables. I created one "intermediate" table with the Cities and created relationships with both tables. I then used this table in a matrix together with the corresponding measures.

MaleClients = CALCULATE(SUM(AllClients[ValueAllocatedIDs1]),AllClients[Sex]="Male")

MalePop = CALCULATE(sum(AllSexPopulation[TotalPopulation]),AllSexPopulation[Sex]="Male")

Percentage from Population = DIVIDE([MaleClients],[MalePop],BLANK())

I used Manchester with no Males to verify the formulas. Below you will find the end result and the other screenshots.

If this solved your problem then please mark it as the solution so others can see it.

[MNedix · Accepted solution]

PS: I've added the file here -> https://www.dropbox.com/scl/fi/x8or0ogq5ufqn69qd8ee7/Talos.pbix?rlkey=vcox1eh83e1lrbme6f69jxxy6&dl=0

[MNedix · Accepted solution]

Hi,

I don't know how your data model looks like but you have to have some relationship between the two tables. I created one "intermediate" table with the Cities and created relationships with both tables. I then used this table in a matrix together with the corresponding measures.

MaleClients = CALCULATE(SUM(AllClients[ValueAllocatedIDs1]),AllClients[Sex]="Male")

MalePop = CALCULATE(sum(AllSexPopulation[TotalPopulation]),AllSexPopulation[Sex]="Male")

Percentage from Population = DIVIDE([MaleClients],[MalePop],BLANK())

I used Manchester with no Males to verify the formulas. Below you will find the end result and the other screenshots.

If this solved your problem then please mark it as the solution so others can see it.

[Talos]

I am linking 'AllClients' to 'AllSexPopulation' based on <City>. But it just adds up the entire male population from the 'AllSexPopulation' table. So, then this intermediary table is necessary?

[MNedix]

It should matter because your relationship is Many-Many and this skews the measures. It should be One-Many. 

[MNedix · Accepted solution]

PS: I've added the file here -> https://www.dropbox.com/scl/fi/x8or0ogq5ufqn69qd8ee7/Talos.pbix?rlkey=vcox1eh83e1lrbme6f69jxxy6&dl=0

[Talos]

I did exaclty as you did but it keeps giving me the total of the male population of the entire country:

(Also, it seems that I have an older version of Power BI than yours (Version: 2.117.984.0 64-bit (May 2023); I cannot update it as it is work/IT department locked))

I believe you that this is the right answer, it is just that it does not work for me (I really do not know why).

Thank you very-very much for all your help (and time you invested in this!!).

[MNedix]

In the visual table, use LACode for cities instead of LocAutName and it should work. This is what I said in the first reply - use the newly created Cities table in the table visual.

[Talos · Accepted solution]

A-haaa! Yes!! If you use the 'Name' field from the joint table 'Cities' and not from within the 'AllClients' or the 'AllPopulation' then it works!!!

Thanks a lot! Have a great day!