Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Apologies for not reply, work commitments! I will upload some more information next week.
Hi @helenmc ,
@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.
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 @helenmc ,
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.
Hi Dino
I've managed to find a solution to this after many hours of trying!
Geographical_Area_Industry_Employment =
CALCULATE(
SUM(BRESData3[Employment]),
ALL(dim_Geographical_Area),
dim_Geographical_Area[Geographical Area] = SELECTEDVALUE(dim_Geographical_Area[Geographical Area]),
dim_Year[Year] = SELECTEDVALUE(dim_Year[Year]),
dim_Broad_Industry, dim_Broad_Industry[Broad Industry] = SELECTEDVALUE(dim_Broad_Industry[Broad Industry]
))
Great_Britain_Industry_Employment =
CALCULATE(
SUM(BRESData3[Employment]),
dim_Geographical_Area[Geographical Area] = "Great Britain",
dim_Year[Year] = SELECTEDVALUE(dim_Year[Year]),
dim_Broad_Industry[Broad Industry] = SELECTEDVALUE(dim_Broad_Industry[Broad Industry])
)
Total_Geographical_Employment =
CALCULATE(
SUM(BRESData3[Employment]),
ALL(dim_Geographical_Area),
dim_Geographical_Area[Geographical Area] = SELECTEDVALUE(dim_Geographical_Area[Geographical Area]),
dim_Year[Year] = SELECTEDVALUE(dim_Year[Year]),
REMOVEFILTERS(dim_Broad_Industry)
)
Total_Great_Britain_Employment =
CALCULATE(
SUM(BRESData3[Employment]),
dim_Year[Year] = SELECTEDVALUE(dim_Year[Year]),
dim_Geographical_Area[Geographical Area] = "Great Britain",
REMOVEFILTERS(dim_Broad_Industry)
)
Location_Quotient =
DIVIDE(
DIVIDE([Geographical_Area_Industry_Employment], [Great_Britain_Industry_Employment]),
DIVIDE([Total_Geographical_Employment], [Total_Great_Britain_Employment])
)
These are five separate measures and I'm not sure how to put them into one measure. I can now filter on area, year and industry and the correct location quotient appears in my matrix and card visuals!
Thanks for all your help on this, it's difficult when we can't send the files but you helped greatly. Any tips on combining this into one measure would be great.
@v-junyant-msft 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 @helenmc ,
Maybe you can try to use sharepoint. You can put your dataset on the sharepoint, set permissions and then copy the sharepoint link here.
Best Regards,
Dino Tao
Thanks for the reply Dino. Unfortunately my organisation won't allow sharing via SharePoint outside the organisation. I'll try and upload some more information later today.
@helenmc 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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
117 | |
109 | |
109 | |
93 | |
69 |
User | Count |
---|---|
173 | |
135 | |
131 | |
96 | |
94 |