Calculate Location Quotient DAX measure

[Anonymous]

Hi Folks,

I hope someone can help me with this, I have the following table in PowerBI and i would like to calculate the location quotion for each line which would be the "%row total" /  ("column total" / "grand total")

Insert photos function is not working correctly to post the image

Area	Sum of Non-Tech	%RT Non-Tech	Sum of Tech	%RT Tech	Total	Total %RT
Ajax	24,570	97.38%	660	2.62%	25,230	100.00%
Brock	2,435	99.59%	10	0.41%	2,445	100.00%
City of Oshawa	45,545	95.90%	1,945	4.10%	47,490	100.00%
City of Pickering	27,195	92.97%	2,055	7.03%	29,250	100.00%
Clarington	19,255	95.75%	855	4.25%	20,110	100.00%
Scugog	6,290	98.90%	70	1.10%	6,360	100.00%
Total	125,290	95.73%	5,595	4.27%	130,885	100.00%

The final table should look like this:

Area	Sum of Non-Tech	%RT Non-Tech	LQ Non-Tech	Sum of Tech	%RT Tech	LQ Tech	Total	Total %RT
Ajax	24,570	97.38%	1.02	660	2.62%	0.61	25,230	100.00%
Brock	2,435	99.59%	1.04	10	0.41%	0.10	2,445	100.00%
City of Oshawa	45,545	95.90%	1.00	1,945	4.10%	0.96	47,490	100.00%
City of Pickering	27,195	92.97%	0.97	2,055	7.03%	1.64	29,250	100.00%
Clarington	19,255	95.75%	1.00	855	4.25%	0.99	20,110	100.00%
Scugog	6,290	98.90%	1.03	70	1.10%	0.26	6,360	100.00%
Total	125,290	95.73%	1.00	5,595	4.27%	1.00	130,885	100.00%

I have filters applied to my data which means that those values will change based on the selected filters.

[Icey · Accepted solution]

Hi @Anonymous ,

I get more information from this post and make an example.

Measures are created like so:

Row Percent 1 =
DIVIDE (
    SUMX ( 'Table', 'Table'[Value1] ),
    SUMX ( 'Table', 'Table'[Value1] + 'Table'[Value2] )
)

Row Percent 2 = 
DIVIDE (
    SUMX('Table','Table'[Value2]),
    SUMX('Table','Table'[Value1]+'Table'[Value2])
)

Row Percent 1 / Grand total Percent =
DIVIDE ( [Row Percent 1], CALCULATE ( [Row Percent 1], ALL ( 'Table' ) ) )

Row Percent 2 / Grand total Percent =
DIVIDE ( [Row Percent 2], CALCULATE ( [Row Percent 2], ALL ( 'Table' ) ) )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.