Forum Discussion
Location quotient calculation
Hi, I'm trying to calculate the location quotient from a data table in my desktop report. This is a snapshot of
The location quotient is calculated as follows using the column headers for example:
(Clifton construction employment for 2015/Total Employment for Clifton 2015)/(GB Construction employment for 2015/Total employment for GB 2015). Hope that makes sense.
I need to calculate the location quotient for each year (2015-2022) for each broad industry. All regions are calculated against the GB (Great Britain) data as this is a measure of relative concentration of employment by industry for the region when compared to the GB average.
I've tried all sorts of ways with DAX but just can't get it. I've resorted to using Excel separate tables.
I want to be able to compare the location quotients for all broad industries for each region and see if there is a change over time. I'm hoping there are DAX measures to sort this out as I also need to do these calculations against other types of sector. Any help or advice much appreciated! 🙂 let me know you any further information is needed.
9 Replies
Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Hi Anonymous ,
Greg_Deckler Thanks for your concern about this case!
Here is my sample data:I can give you an example for Clifton:
location quotient = VAR Clifton_construction_employment = CALCULATE( SUM('Table'[Employment]), FILTER( ALLEXCEPT('Table', 'Table'[Year]), 'Table'[Broad Industry] = "Construction" && LEFT('Table'[Region], 7) = "Clifton" ) ) VAR Total_Employment_fo_Clifton = CALCULATE( SUM('Table'[Employment]), FILTER( ALLEXCEPT('Table', 'Table'[Year]), LEFT('Table'[Region], 7) = "Clifton" ) ) VAR GB_Construction_employment = CALCULATE( SUM('Table'[Employment]), FILTER( ALLEXCEPT('Table', 'Table'[Year]), 'Table'[Broad Industry] = "Construction" && 'Table'[Region] = "GB" ) ) VAR Total_employment_for_GB = CALCULATE( SUM('Table'[Employment]), FILTER( ALLEXCEPT('Table', 'Table'[Year]), 'Table'[Region] = "GB" ) ) VAR _Clifton = DIVIDE(Clifton_construction_employment, Total_Employment_fo_Clifton) VAR _GB = DIVIDE(GB_Construction_employment, Total_employment_for_GB) RETURN DIVIDE(_Clifton, _GB)The final output is as below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Anonymous many thanks for your quick response, much appreciated! Nearly there with this and taking into account Greg_Deckler comments, I would like to send a file with the data but can't find a way to do this through this forum. It is difficult to use a sample table and fo the calculation if you don't have the complete data set as it uses total employment across all industries for each year. This is the output I need for each Region and Industry:
Location Quotient - Clifton 2015 2016 2017 2018 2019 2020 2021 2022 Broad Industry LQ LQ LQ LQ LQ LQ LQ LQ Agriculture, Forestry And Fishing 0.00 0.18 0.00 0.00 0.00 0.00 0.00 0.00 Mining And Quarrying 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Manufacturing 0.08 0.18 0.10 0.17 0.18 0.06 0.14 0.16 Electricity, Gas, Steam And Air Conditioning Supply 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Water Supply; Sewerage, Waste Management And Remediation Activities 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Construction 1.53 1.44 1.48 0.78 0.85 0.64 1.02 1.38 Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles 0.92 0.82 0.91 1.17 1.25 1.37 1.41 1.24 Transportation And Storage 0.74 0.67 0.67 0.72 0.73 0.86 0.80 0.64 Accommodation And Food Service Activities 0.91 1.40 1.35 1.39 1.43 1.56 1.37 1.25 Information And Communication 0.08 0.17 0.23 0.29 0.20 0.28 0.21 0.10 Financial And Insurance Activities 0.09 0.08 0.12 0.12 0.33 0.22 0.09 0.18 Real Estate Activities 0.17 0.16 0.00 0.14 0.15 0.31 0.39 0.51 Professional, Scientific And Technical Activities 1.02 1.24 0.90 0.89 1.14 0.92 0.96 0.89 Administrative And Support Service Activities 0.75 0.47 0.46 0.65 0.75 0.51 0.66 1.12 Public Administration And Defence; Compulsory Social Security 0.43 0.74 0.40 0.39 0.40 0.41 0.40 0.38 Education 2.91 2.38 2.49 2.36 2.27 2.24 2.07 2.04 Human Health And Social Work Activities 1.49 1.63 1.85 1.65 1.27 1.55 1.38 1.38 Arts, Entertainment And Recreation 0.50 0.33 0.65 0.66 0.63 0.79 0.64 0.59 Other Service Activities 1.83 1.83 1.54 1.64 1.91 1.14 1.98 1.65 I need the table visuals in power bi as above for each region and be able to visualise changes over time. If filtering by Broad Industry, region or year, the location quotient shouldn't change in these tables. Please could you let me know if there is a way to send you the file? It is all publicly available data, so no issues about sending this to you.
Many thanks!
Hi Dino
I've tried this and it does work for the specified sector and region, thank you. My objective was to make this dynamic so that filters can be used instead of specifying each individual sector and industry in measures. To get each location quotient for each sector for each year is a lot of dax measures if I have to specify within the measure which sector and industry. Is there any way to make this dynamic by user selection? There might not be, so the alternative would be to do the calculations in Excel and upload additional tables?
Many thanks for your time on this!
Helen
Hi Anonymous ,
Since I don't have your complete data, I'm not quite sure if my suggestion will work.
To make this dynamic by user selection, you can try to use slicer. For example, you can add new tables for column Broad Industry and Region without any relationships and use these tables to create slicers.
In this case, DAX needs to be modified, for example:VAR GB_Construction_employment = CALCULATE( SUM('Table'[Employment]), FILTER( ALLEXCEPT('Table', 'Table'[Year]), 'Table'[Broad Industry] = "Construction" && 'Table'[Region] = "GB" ) )It needs to be modified to:
VAR GB_Construction_employment = CALCULATE( SUM('Table'[Employment]), FILTER( ALLEXCEPT('Table', 'Table'[Year]), 'Table'[Broad Industry] = SELECTEDVALUE('Slicer_1'[Broad Industry]) && 'Table'[Region] = SELECTEDVALUE('Slicer_2'[Region]) ) )'Slicer_1' is new tables for column Broad Industry and 'Slicer_2' is new tables for Region.
Please try whether this will help.
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
