cancel
Showing results for
Did you mean:
Helper II

## Divide Count of one column measure by column value of another table

Hi Everyone,

I am try to divide the Count measure Count(Table1[City]) by the column value (Number) from another table for each country. I have joined Table1 and Table2 with 1 to Many connection.

Table1:

 Country City USA New York USA Miami USA New York UK London UK London

Table2:

 Country Number INDIA 5 UK 6 USA 2

Measure:

count_city = Count(Table1[City]) ---> This gives me the count of the city

For Example lets consider UK and City London.

I want to achieve Count_City/6

Count_City is the count of city London from Table1 and 6 is coming from Table2.

1 ACCEPTED SOLUTION

Accepted Solutions
Solution Sage

@nikeshv, try this measure:

``````Ratio =
VAR vCityCount =
COUNT ( Table1[City] )
VAR vCurCountry =
MAX ( Table1[Country] )
VAR vCountryTable =
FILTER ( ALL ( Table2 ), Table2[Country] = vCurCountry )
VAR vCountryNumber =
MAXX ( vCountryTable, Table2[Number] )
RETURN
DIVIDE ( vCityCount, vCountryNumber )``````
2 REPLIES 2
Solution Sage

@nikeshv, try this measure:

``````Ratio =
VAR vCityCount =
COUNT ( Table1[City] )
VAR vCurCountry =
MAX ( Table1[Country] )
VAR vCountryTable =
FILTER ( ALL ( Table2 ), Table2[Country] = vCurCountry )
VAR vCountryNumber =
MAXX ( vCountryTable, Table2[Number] )
RETURN
DIVIDE ( vCityCount, vCountryNumber )``````
Super User III

Hi,

In Table1, write this calculated column formula = RELATED('Table2'[Number]) and name the column as Number.  To your Table visual, drag Country from Table2 and City from Table1.  Write this measure

=countrows(Table1)/min(Table1[Number])

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform October Community Highlights

Check out the top community contributors across all of the communities

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors