DAX % mix within column, plus cumulative total of that column.

[M1donne]

Hi all,

Happy Sunday.

I have a table of data with several columns, I would like to calculate a couple of new measures..

I want to calculate a Percentage Mix of each record in a particular column in the table, to be then be able to use this in a table (visualization), I would also then like to create a new measure or column (advice required) that provides a cumulative % on the column that has the percentage mix.

I have been exploring ways to do this but to no avail.

I would really appreciate any help or insight.

Regards

MD

 

[M1donne]

Hi all,

Just a clarification of my requirements..

I have a table (visualisation) with several columns, each of which have summarised totals...

What I'm trying to create is a couple of additional columns, one of which is easy (and has been done) by using the '% Grand total' quick calc.

I want to take the % GT column result and use it in another calculated column i.e. using the % as part of another calculation.

I'm conscious it may be not be explained particularly well but I'm hoping someone can help.

Regards

Md

 

[M1donne]

Okay,

I'm getting somewhere - slowly

My table is as below

Excel 2010
	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
4	Article	ASP	Cost	Sales units	Total sales (£)	Total Cost (£)	Grand Total Sales (£)	Sales mix	Total Articles	Article %	Total Sales (units)	Sales Pareto Mix	Pareto %	Cumulative Units
5	1	2.99	1.196	10	29.9	11.96	39701.27	0.08 %	20	5 %	6873	344	19 %	1333
6	2	4.99	1.996	76	379.24	151.696	39701.27	0.96 %	20	10 %	6873	687	34 %	2342
7	3	3.99	1.596	55	219.45	87.78	39701.27	0.55 %	20	15 %	6873	1,031	45 %	3109
8	4	6.99	2.796	44	307.56	123.024	39701.27	0.77 %	20	20 %	6873	1,375	54 %	3701
9	5	9.99	3.996	33	329.67	131.868	39701.27	0.83 %	20	25 %	6873	1,718	61 %	4166
10	6	12.99	5.196	6	77.94	31.176	39701.27	0.20 %	20	30 %	6873	2,062	66 %	4546
11	7	2.99	1.196	667	1994.33	797.732	39701.27	5.02 %	20	35 %	6873	2,406	71 %	4867
12	8	4.99	1.996	876	4371.24	1748.496	39701.27	11.01 %	20	40 %	6873	2,749	75 %	5149
13	9	3.99	1.596	646	2577.54	1031.016	39701.27	6.49 %	20	45 %	6873	3,093	79 %	5404
14	10	6.99	2.796	987	6899.13	2759.652	39701.27	17.38 %	20	50 %	6873	3,437	82 %	5638
15	11	9.99	3.996	234	2337.66	935.064	39701.27	5.89 %	20	55 %	6873	3,780	85 %	5853
16	12	12.99	5.196	456	5923.44	2369.376	39701.27	14.92 %	20	60 %	6873	4,124	88 %	6050
17	13	2.99	1.196	775	2317.25	926.9	39701.27	5.84 %	20	65 %	6873	4,467	91 %	6226
18	14	4.99	1.996	788	3932.12	1572.848	39701.27	9.90 %	20	70 %	6873	4,811	93 %	6379
19	15	3.99	1.596	443	1767.57	707.028	39701.27	4.45 %	20	75 %	6873	5,155	95 %	6507
20	16	6.99	2.796	567	3963.33	1585.332	39701.27	9.98 %	20	80 %	6873	5,498	96 %	6610
21	17	9.99	3.996	34	339.66	135.864	39701.27	0.86 %	20	85 %	6873	5,842	97 %	6690
22	18	12.99	5.196	33	428.67	171.468	39701.27	1.08 %	20	90 %	6873	6,186	98 %	6754
23	19	2.99	1.196	66	197.34	78.936	39701.27	0.50 %	20	95 %	6873	6,529	99 %	6810
24	20	16.99	6.796	77	1308.23	523.292	39701.27	3.30 %	20	100 %	6873	6,873	100 %	6871
Sheet1

I now need to split mu cumulative column into its individual 'cell' values......help.

Regards

Md