cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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)

3 ACCEPTED SOLUTIONS
Super User

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.

Super User
Frequent Visitor

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!

7 REPLIES 7
Super User

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.

Frequent Visitor

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?

Super User

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

Super User
Frequent Visitor

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!!).

Super User

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.

Frequent Visitor

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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors