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
Super User II

@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
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
Super User II

@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 )``````

Announcements

Happy New Year from Power BI

This is a must watch for a message from Power BI!