Min of Sum

[Anonymous]

Hi,

how can I calculate the min values of summarized table? I tried several thing already with sumx in combination with min and summarize but still no luck. In the output table the first three columns are dimensions rest of should be calculated were the min of columns is the lowest value per Type summarized

Input Table:

Detail	Type	Size	Amount
01. Straight	Straight	H	2
01. Straight	Straight	L	10
01. Straight	Straight	XS	10
01. Straight	Straight	M	2
01. Straight	Straight	S	6
01. Straight	Straight	XL	3
02. Small	Small	H	3
02. Small	Small	L	6
02. Small	Small	XS	3
02. Small	Small	M	9
02. Small	Small	S	9
02. Small	Small	XL	4
03. Straight	Straight	H	6
03. Straight	Straight	L	9
03. Straight	Straight	XS	5
03. Straight	Straight	M	6
03. Straight	Straight	S	9
03. Straight	Straight	XL	7
04. High	High	H	1
04. High	High	L	8
04. High	High	XS	4
04. High	High	M	2
04. High	High	S	9
04. High	High	XL	10
05. High	High	H	1
05. High	High	L	6
05. High	High	XS	6
05. High	High	M	8
05. High	High	S	8
05. High	High	XL	1
06. Medium	Medium	H	7
06. Medium	Medium	L	9
06. Medium	Medium	XS	7
06. Medium	Medium	M	1
06. Medium	Medium	S	8
06. Medium	Medium	XL	7
07. Straight	Straight	H	1
07. Straight	Straight	L	1
07. Straight	Straight	XS	3
07. Straight	Straight	M	9
07. Straight	Straight	S	1
07. Straight	Straight	XL	7
08. Straight	Straight	H	8
08. Straight	Straight	L	1
08. Straight	Straight	XS	2
08. Straight	Straight	M	7
08. Straight	Straight	S	2
08. Straight	Straight	XL	4
09. Small	Small	H	8
09. Small	Small	L	9
09. Small	Small	XS	7
09. Small	Small	M	3
09. Small	Small	S	3
09. Small	Small	XL	5
10. Straight	Straight	H	7
10. Straight	Straight	L	8
10. Straight	Straight	XS	4
10. Straight	Straight	M	3
10. Straight	Straight	S	5
10. Straight	Straight	XL	3
11. Small	Small	H	3
11. Small	Small	L	4
11. Small	Small	XS	7
11. Small	Small	M	10
11. Small	Small	S	10
11. Small	Small	XL	2
12. Small	Small	H	8
12. Small	Small	L	3
12. Small	Small	XS	3
12. Small	Small	M	9
12. Small	Small	S	3
12. Small	Small	XL	5
13. Small	Small	H	3
13. Small	Small	L	4
13. Small	Small	XS	6
13. Small	Small	M	8
13. Small	Small	S	10
13. Small	Small	XL	9
14. High	High	H	6
14. High	High	L	10
14. High	High	XS	7
14. High	High	M	7
14. High	High	S	6
14. High	High	XL	4
15. Medium	Medium	H	3
15. Medium	Medium	L	5
15. Medium	Medium	XS	4
15. Medium	Medium	M	6
15. Medium	Medium	S	4
15. Medium	Medium	XL	10
16. Small	Small	H	3
16. Small	Small	L	6
16. Small	Small	XS	6
16. Small	Small	M	1
16. Small	Small	S	3
16. Small	Small	XL	5

Output Table

Size	Type	Sum of Amount	Min of	Delta
H	High	8	8	0
H	Medium	10	7	3
H	Small	28	28	0
H	Straight	24	23	1
L	High	24	8	16
L	Medium	14	7	7
L	Small	32	28	4
L	Straight	29	23	6
M	High	17	8	9
M	Medium	7	7	0
M	Small	40	28	12
M	Straight	27	23	4
S	High	23	8	15
S	Medium	12	7	5
S	Small	38	28	10
S	Straight	23	23	0
XL	High	15	8	7
XL	Medium	17	7	10
XL	Small	30	28	2
XL	Straight	24	23	1
XS	High	17	8	9
XS	Medium	11	7	4
XS	Small	32	28	4
XS	Straight	24	23	1

[tamerj1 · Accepted solution]

Hi @Anonymous 
Please refer to attached sample file with the solution

Min of = 
SUMX (
    SUMMARIZE ( 'Table', 'Table'[Size], 'Table'[Type] ),
    MINX (
        CALCULATETABLE ( 
            SUMMARIZE ( 'Table', 'Table'[Size], 'Table'[Type] ),
            ALLEXCEPT ( 'Table', 'Table'[Type] )
        ),
        [Sum of Amount]
    )
)

[Anonymous]

Hello @Anonymous 
please follow the instruction:
i created a sample data from the bug table you provided,

then i summarzied the table with the following calculated table formula:

Summarized Table = SUMMARIZE(Powerbi,Powerbi[Size],Powerbi[Type],"Sum Of", SUM(Powerbi[Amount]))

now create a new column:

Min Of = CALCULATE(MIN('Summarized Table'[Sum Of]),ALLEXCEPT('Summarized Table','Summarized Table'[Type]))

the allexcpet basically filters the min of sum for each size

now just add another column for delta

Delta = 'Summarized Table'[Sum Of] - 'Summarized Table'[Min Of]

 

if you find this post helpful id appreciate a kudos(like) and kindly accept this as a solution for other users