cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
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:

CountryCity
USANew York
USAMiami
USANew York
UKLondon
UKLondon

 

Table2:

CountryNumber
INDIA5
UK6
USA2

 

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

View solution in original post

2 REPLIES 2
Super User III
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
https://www.linkedin.com/in/excelenthusiasts/
Super User II
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 )

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

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

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors