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.
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.
Go to Solution.
@nikeshv, try this measure:
VAR vCityCount =
COUNT ( Table1[City] )
VAR vCurCountry =
MAX ( Table1[Country] )
VAR vCountryTable =
FILTER ( ALL ( Table2 ), Table2[Country] = vCurCountry )
VAR vCountryNumber =
MAXX ( vCountryTable, Table2[Number] )
DIVIDE ( vCityCount, vCountryNumber )
View solution in original post
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
Hope this helps.
Check out the on demand sessions that are available now!
Check out the Winners!
Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.