Measure to Divide with related column by category

[Anonymous]

Hello all,

I tried to find the solution in the topics but can't seem to put the right words on it :

I have :

- 1 table "PARC" with lines representing installations on sites by state

- 1 table "POP" with population by state.

Relation Many to 1 by field State

I would like to calculate by region the coverage rate  e.g. number of installations divided by population.

I tried a measure

= Groupby(PARC[State], "State", DIVIDE ( DISTINCTCOUNT(  PARC[ID_site], Related (POP[Population_by_region]))))

And I have 2 errors : the related doesn't work and groupby isn't happy I use an argument not in direct link with group by.

Of course, this is part of a bigger project, otherwise I wouldn't have used PBI !

Thanks a lot !

[parry2k]

@Anonymous Also check my YT video to learn more about RELATED and RELATEDTABLE functions https://youtu.be/AOWbieiqVQ0

[parry2k]

@Anonymous I think this is what you need:

New Measure = 
DIVIDE ( SUM ( Parc[Population] ), COUNTROWS ( RELATEDTABLE ( POP ) ) )

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[Anonymous · Accepted solution]

It was almost it, actually I don't want to count the lines but distinct ID site.

I found this solution working : 

taux = DIVIDE(CALCULATE(DISTINCTCOUNT(PARC[Id site])),Feuil1[Population])

At first I thought CALCULATE could only SUM when really it can work with any function.

Thank you !