many sum of mins

[MDL88]

Hello All,

I am hoping someone can assist. I am a new user and have been stumped with this problem.

I have a table with a list of products and then an inventory table. The column "Product Key" in the inventory table relates all similar products together. There is also a "Supply chain level" column which relates the materials together at a processing step.

I need to be able to SUM up the Supply Column by like products in the product key, then add the MIN summed number per Supply chain Level together.

I have included a sample table below and an attached illistration of how I visualize getting the answer. 

If there is a better table structure, measure, or calculated column that someone can recommend I would be grateful. At the end of the day, I am looking to have a the single number in the green box that can be filtered with a few slicers to remove Material, Supply, & Report date.
Thank you all for taking the time to read this. I truly appreciate any support on this. 

-Matt

Material	Plnt	Supply	Report Date	Key	Product Key (sum)	Supply Chain Level (min)
A	1000	27	11-12-2021	1000_A	1	1
A	1000	26	11-15-2021	1000_A	1	1
A	2000	25	11-12-2021	2000_A	2	2
A	2000	24	11-15-2021	2000_A	2	2
B	2000	1000	11-12-2021	2000_B	3	2
B	2000	1000	11-15-2021	2000_B	3	2
C	2000	-1	11-12-2021	2000_C	4	4
C	2000	-1	11-15-2021	2000_C	4	4
D	2000	-1	11-12-2021	2000_D	5	5
D	2000	-1	11-15-2021	2000_D	5	5
E	2000	-1	11-12-2021	2000_E	6	6
E	2000	-1	11-15-2021	2000_E	6	6
O	6000	118	11-12-2021	6000_O	8	8
R	6000	-1	11-12-2021	6000_R	8	8
O	6000	118	11-15-2021	6000_O	8	8
R	6000	-1	11-15-2021	6000_R	8	8
M	6000	-1	11-12-2021	6000_M	10	8
M	6000	-1	11-15-2021	6000_M	10	8
S	6000	28	11-12-2021	6000_S	11	8
P	6000	1000	11-12-2021	6000_P	11	8
V	6000	190	11-12-2021	6000_V	11	8
S	6000	27	11-15-2021	6000_S	11	8
P	6000	1000	11-15-2021	6000_P	11	8
V	6000	193	11-15-2021	6000_V	11	8
Q	6000	-10	11-12-2021	6000_Q	12	8
N	6000	1000	11-12-2021	6000_N	12	8
F	3000	-4	11-12-2021	3000_F	12	8
Q	6000	-15	11-15-2021	6000_Q	12	8
N	6000	1000	11-15-2021	6000_N	12	8
F	3000	-5	11-15-2021	3000_F	12	8

[v-yalanwu-msft · Accepted solution]

Hi, @MDL88 ;

You also could try it.

SUM = 
CALCULATE (
    SUM ( 'Table'[Supply] ),
  FILTER(ALL('Table'),[Product Key (sum)]=MAX('Table'[Product Key (sum)])))

Min = 
var _a=SUMMARIZE('Table',[Supply Chain Level (min)],"1", MINX(ALLEXCEPT('Table','Table'[Supply Chain Level (min)]),[SUM]))
return SUMX(_a,[1])

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @MDL88 ,

Try creating the following 3 measures:

SUM Supply chain = 
CALCULATE (
    SUM ( 'Table'[Supply] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Product Key (sum)] = MAX ( 'Table'[Product Key (sum)] )
    )
)

Min sum supply chain product key = 
VAR Temp_table =
    FILTER (
        SUMMARIZE (
            ALLSELECTED ( 'Table' ),
            'Table'[Product Key (sum)],
            "SUMSP", [SUM Supply chain]
        ),
        'Table'[Product Key (sum)] <= MAX ( 'Table'[Product Key (sum)] )
    )
RETURN
    MINX ( Temp_table, [SUMSP] )




Min Supply chain SUM =
VAR Temp_table =
    GROUPBY (
        SUMMARIZE ( 'Table', 'Table'[Key], "MinSP", [Min sum supply chain product key] ),
        [MinSP]
    )
RETURN
    IF (
        HASONEVALUE ( 'Table'[Key] ),
        [Min sum supply chain product key],
        SUMX ( Temp_table, [MinSP] )
    )

The second measure is just for calculation purposes so don't use it on your table, use the first and the third.