How to divide each row in a column by the sum of all rows within a group (Calculated column)

[Anonymous]

Hello,

I would like to know how to calculate the ratio of each row divided by the sum of the same rows within a group using calculated column.

Regards,

projectskey	fiscalPeriod	Forecast Distribution	Sum of Forecast Distribution	Factor
1299647	202001	0.01	1.23	0.00813
1299647	202002	0.04	1.23	0.03252
1299647	202003	0.08	1.23	0.065041
1299647	202004	0.15	1.23	0.121951
1299647	202005	0.22	1.23	0.178862
1299647	202006	0.22	1.23	0.178862
1299647	202007	0.22	1.23	0.178862
1299647	202008	0.15	1.23	0.121951
1299647	202009	0.08	1.23	0.065041
1299647	202010	0.04	1.23	0.03252
1299647	202011	0.01	1.23	0.00813
1299647	202012	0.01	1.23	0.00813
1301420	202001	0.01	1.15	0.008696
1301420	202002	0.04	1.15	0.034783
1301420	202004	0.15	1.15	0.130435
1301420	202005	0.22	1.15	0.191304
1301420	202006	0.22	1.15	0.191304
1301420	202007	0.22	1.15	0.191304
1301420	202008	0.15	1.15	0.130435
1301420	202009	0.08	1.15	0.069565
1301420	202010	0.04	1.15	0.034783
1301420	202011	0.01	1.15	0.008696
1301420	202012	0.01	1.15	0.008696

[amitchandak]

@Anonymous ,

use all except

example

calculate(sum(Table[column),allexcept(Table,Table[projectskey]))

Also, refer

https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390

[Anonymous]

Here's the Dax that give me the results of the 5th column.

Base Forecast Distribution Per Project =

calculate(sum('Project''s Info'[Target % Revenue Completed]),allexcept('Project''s Info','Project''s Info'[projectskey],'Project''s Info'[fiscalPeriod]))/

calculate(sum('Project''s Info'[Target % Revenue Completed]),allexcept('Project''s Info','Project''s Info'[projectskey]))

Thanks you very much for your help.

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

Try

Column = 'Table'[Forecast Distribution]/CALCULATE(SUM('Table'[Forecast Distribution]),ALLEXCEPT('Table','Table'[projectskey]))

 

Best Regards,

Jay

[Anonymous]

This Dax gives me the 4th column .  I wonder if it is possible to divide the 3th column by the 4 th column to obtain the results show in the 5th column without generating a circular dependency.