Get percent value from total sum row in pivot mode

user_10717265 0
Viewed 83

i have next data in pivot mode my data in pivot mode

pivot query

database('MyDatabase').Test
| summarize AdjValue = sum(AdjValue) by Fylke, ClassSE
| extend p = pack(ClassSE, AdjValue)
| summarize bag=make_bag(p) by Fylke
| evaluate bag_unpack(bag)

need to devide each value on rowSum - (value / rowSum * 100 = some percent).

I tried to use join for temporary pivot table but not succesfull. Plese help.

expected result in pivot mode

kql azure-data-explorer
1 Answers
// This is not a part of the solution only generation of a sample dataset 
let Test = materialize(range i from 1 to 100 step 1 | extend AdjValue = rand()*100, Fylke = strcat('Fylke_',tostring(toint(rand()*10))), ClassSE = strcat('ClassSE_',tostring(toint(rand()*5))));
// The solution starts here
let sum_by_Fylke_ClassSE = materialize(Test | summarize AdjValue = sum(AdjValue) by Fylke,ClassSE);
let sum_by_Fylke = sum_by_Fylke_ClassSE | summarize Fylke_AdjValue = sum(AdjValue) by Fylke;
sum_by_Fylke
| join sum_by_Fylke_ClassSE on Fylke
| evaluate pivot(ClassSE, sum(AdjValue/Fylke_AdjValue*100), Fylke)
| order by Fylke asc
Fylke ClassSE_0 ClassSE_1 ClassSE_2 ClassSE_3 ClassSE_4
Fylke_0 49.395106915030119 46.755319585100125 0 0 3.8495734998697557
Fylke_1 62.292139898464924 5.2693450408156046 7.6552025348509991 6.2015378618740726 18.581774663994409
Fylke_2 50.145053387669094 1.2587789001232987 41.166356893005975 7.4298108192016352 0
Fylke_3 10.564746410722819 35.571795098974818 9.817452610031193 6.7291651195813156 37.316840760689857
Fylke_4 0 11.770542330107656 25.250380537085615 12.46115402880039 50.517923104006343
Fylke_5 11.098011115225455 24.401878297613749 37.849873348947106 16.221012456995606 10.429224781218091
Fylke_6 31.340691613236839 53.496440433838153 0 15.16286795292501 0
Fylke_7 31.764625835537881 34.741929615153026 7.9119328065215306 6.2721731408556778 19.309338601931888
Fylke_8 25.3982395190392 32.868425203681305 28.605169017331683 3.0705116629208007 10.057654597027003
Fylke_9 14.778417432435949 29.9861720571239 19.118237524156271 15.091700930745427 21.025472055538462

Fiddle

user_63364790
Related