Sum a column in a summarized table

[mounika_g04]

Hi Team,

I want to sum one particular column in a summarized table (say t - which is created using actual data). I used below formula but giving wrong number:

SUMX(SUMMARIZE('ACTUAL TABLE NAME','ACTUAL TABLE NAME'[COLUMN NAME 1],"pd1",AVERAGEX(DISTINCT('ACTUAL TABLE NAME'[COLUMN NAME 2]),CALCULATE(AVERAGE('ACTUAL TABLE NAME'[COLUMN NAME 3]))),"sos1",CALCULATE([MEASURE])),[pd1]*[sos1])

Attached summarized table output for reference. Need to sum the column named "pd1 * sos1" = 42.85% but giving ~90%

LEVEL	pd1	sos1	pd1 * sos1
KW1	0.50%	59.54%	0.30%
KW2	4.00%	0.00%	0.00%
KW3		67.35%
KW4	1.50%	27.09%	0.41%
KW5		27.09%
KW6		27.09%
KW7	1.00%	0.00%	0.00%
KW8	1.00%	0.00%	0.00%
KW9	7.00%	83.63%	5.85%
KW10	30.00%	48.96%	14.69%
KW11		90.03%
KW12	0.50%	90.03%	0.45%
KW13	1.00%	58.12%	0.58%
KW14	11.00%	58.12%	6.39%
KW15	2.50%	14.88%	0.37%
KW16	0.70%	27.09%	0.19%
KW17	1.50%	25.90%	0.39%
KW18		0.00%
KW19		5.56%
KW20		77.50%
KW21		14.73%
KW22	0.50%	10.42%	0.05%
KW23	1.00%	12.50%	0.13%
KW24		83.48%
KW25	1.00%	83.48%	0.83%
KW26	1.50%	48.96%	0.73%
KW27	4.00%	6.62%	0.26%
KW28	1.50%	75.00%	1.13%
KW29		22.44%
KW30		100.00%
KW31	2.00%	14.73%	0.29%
KW32		8.41%
KW33		8.41%
KW34	4.00%	10.42%	0.42%
KW35		29.77%
KW36	0.50%	12.50%	0.06%
KW37		15.10%
KW38	0.50%	15.10%	0.08%
KW39	0.50%	18.74%	0.09%
KW40		78.84%
KW41		36.47%
KW42		45.23%
KW43	0.50%	97.47%	0.49%
KW44	16.00%	54.16%	8.66%
KW45		1.63%

Thanks in advance

Regards,

Mounika G

[v-yanjiang-msft]

Hi @mounika_g04 ,

Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team _ kalyj

[v-yanjiang-msft]

Hi @mounika_g04 ,

Is the attached summarized table obtained by copying the SUMMARIZE part of the formula and pasting it in Modeling>New table?

If not, try to do it like that and check if the pd1 and sos1 columns in the new table can get the expected result.

I create a sample and can get the correct result by your formula, not sure where the problem is, you can download my sample to check.

Look forward to your reply.

Best Regards,
Community Support Team _ kalyj

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

[tamerj1]

Hi @mounika_g04 

is [Column Name] in your formula the [Level] column?

[mounika_g04]

Column name 1 is level column, but column name 2 and column name 3 are different (as in same table but different column names which I dont need in summary table) and are in base table

[tamerj1]

@mounika_g04 

What is the relationship between these two tables?

[mounika_g04]

All the columns are in the base table (summary table is created using one single base table)