Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
helenmc
Frequent Visitor

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:

helenmc_1-1713537072323.png

(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 9
helenmc
Frequent Visitor

Apologies for not reply, work commitments!  I will upload some more information next week. 

v-junyant-msft
Community Support
Community Support

Hi @helenmc ,

@Greg_Deckler Thanks for your concern about this case!

Here is my sample data:

vjunyantmsft_0-1713757612048.png

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:

vjunyantmsft_1-1713757673546.png

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 - Clifton20152016201720182019202020212022
Broad IndustryLQLQLQLQLQLQLQLQ
Agriculture, Forestry And Fishing0.000.180.000.000.000.000.000.00
Mining And Quarrying0.000.000.000.000.000.000.000.00
Manufacturing0.080.180.100.170.180.060.140.16
Electricity, Gas, Steam And Air Conditioning Supply0.000.000.000.000.000.000.000.00
Water Supply; Sewerage, Waste Management And Remediation Activities0.000.000.000.000.000.000.000.00
Construction1.531.441.480.780.850.641.021.38
Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles0.920.820.911.171.251.371.411.24
Transportation And Storage0.740.670.670.720.730.860.800.64
Accommodation And Food Service Activities0.911.401.351.391.431.561.371.25
Information And Communication0.080.170.230.290.200.280.210.10
Financial And Insurance Activities0.090.080.120.120.330.220.090.18
Real Estate Activities0.170.160.000.140.150.310.390.51
Professional, Scientific And Technical Activities1.021.240.900.891.140.920.960.89
Administrative And Support Service Activities0.750.470.460.650.750.510.661.12
Public Administration And Defence; Compulsory Social Security0.430.740.400.390.400.410.400.38
Education2.912.382.492.362.272.242.072.04
Human Health And Social Work Activities1.491.631.851.651.271.551.381.38
Arts, Entertainment And Recreation0.500.330.650.660.630.790.640.59
Other Service Activities1.831.831.541.641.911.141.981.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. 

Greg_Deckler
Super User
Super User

@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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.