Calculate Location Quotient DAX measure

[RajaaB]

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%

"LQ Non-Tech" column was calculated as follows:

Area	LQ Non-Tech
Ajax	97.38% / 95.73% =
Brock	99.59% / 95.73% =
City of Oshawa	95.90% / 95.73% =
City of Pickering	92.97% / 95.73% =
Clarington	95.75% / 95.73% =
Scugog	98.90% / 95.73% =
Total	95.73% / 95.73%

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

Thanks,

[v-yuta-msft]

@RajaaB ,

The given info is not enough. I guess '%RT Non-Tech' is a measure, right? Could you share the dax code so that I could analysis it?

Regards,

Jimmy Tao

[RajaaB]

@v-yuta-msft ,

I calculated the '%RT Non-Tech' using this measure:

%RT Non-TECH = DIVIDE(SUM( POW_NOCS_UPOW_2016[  Usual place of work] ),
        CALCULATE(
            SUM( POW_NOCS_UPOW_2016[  Usual place of work] ),
            ALLSELECTED( POW_NOCS_UPOW_2016 )
        )
    ) 

I was also able to get the same results using the "show values as percent of row total":

thanks,