Mesuare sum each by grup with filter

[Analitika]

I have a table

Date	NameCode	SubNameCode		Name	Name Price	Sub name	SubName price	Pcs
43490	10070	131		Tea		Salary	0,061705	0
43490	10070	6000		Tea		Sticker1	0,001185	2010
43490	10070	421000		Tea		Stick1	0,00194	2400
43490	10070	10070		Tea	0,31777			2000
43490	10070	20310001		Tea		Leaf1	0,204945	102500
43490	10070	401000		Tea		Stick2	0,004275	3520
43490	10070	310050		Tea		Box1	0,04372	2024
43490	10070			Tea			0	2000
43490	10070	6032		Tea			0	0
43514	10070	131		Tea		Salary	0,060883	0
43514	10070	6000		Tea		Sticker1	0,001187	1024
43514	10070	20310001		Tea		Leaf1	0,003788	1793
43514	10070	401000		Tea		Stick2	0,201197	51250
43514	10070	10070		Tea	0,30843			1019
43514	10070	310050		Tea		Box1	0,041374	976
43514	10070			Tea			0	1019
43514	10070	6032		Tea			0	0

Need measure to get sum of each Subname by SubName price and Sum of each SubName Pcs
so must be something like that

AllSubNamePrice =
Calculate(

SUM([SubnamePrice]),
FIlter([NameCode]<>[SubNameCode]),
)

AllSubNameCount =
Calculate(

SUM([Pcs]),
FIlter([NameCode]<>[SubNameCode]),
)

1pcsPrice =
Divide(

AllSubNamePrice ,
AllSubNameCount ), // if  "Salary", then get value from [Pcs] where [NameCode]=[SubNameCode]
)

But measure sum all alue but not by group and dates

Expected result is:

Sub name	Sum	Count	1pcs
Salary	0,1226 €	-	0,0614276 €
Box1	0,0851 €	3 000	0,0000284 €
Leaf1	0,2087 €	104 293	0,0000020 €
Stick1	0,0019 €	2 400	0,0000008 €
Stick2	0,2055 €	54 770	0,0000038 €
Sticker1	0,0024 €	3 034	0,0000008 €

[v-alq-msft · Accepted solution]

Hi, @Analitika 

Based on your description, you may create three measures as below.

AllSubNamePrice = 
CALCULATE(
    SUM('Table'[SubName price]),
    FILTER(
        'Table',
        'Table'[NameCode]<>'Table'[SubNameCode]
    )
)
AllSubNameCount = 
IF(
    SELECTEDVALUE('Table'[Sub name])<>"Salary",
    CALCULATE(
        SUM('Table'[Pcs]),
        FILTER(
            'Table',
            'Table'[Name Price]<>'Table'[SubNameCode]
        )
    ),
    CALCULATE(
                SUM('Table'[Pcs]),
                FILTER(
                    ALL('Table'),
                    'Table'[NameCode]='Table'[SubNameCode]
                )
    )
)
1pcsPrice = 
IF(
    NOT(ISBLANK(SELECTEDVALUE('Table'[Sub name]))),
    DIVIDE(
            [AllSubNamePrice],
            [AllSubNameCount]
    )
)

Result:

Best Regards

Allan

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

[Analitika]

Thank You, Works good, except finding PCS for Salary, always zeroes. But needed find value wich in column PCS with same date, but not All() need be in group by Name.

So must be something like:

If SubName = "Salary" Then take value from column PCS where 'Table'[NameCode]='Table'[SubNameCode] and same date

that because i need blanks too