Distinctcount in an if statement with conditions

[jameshoneywill]

Hi 

My fact table contains multiple rows for the same item every month. I have a measure that sums the total quantity based on when the attribute is Volume Units

What I am looking for is a distinct count of the Product UIN when the product is beans

This is the fact table;

Product UI	Product Group	Period	Attribute	Value
22	Beans	01/01/2023	Profit	5
22	Beans	01/01/2023	Profit	5
22	Beans	01/01/2023	Profit	5
33	Toast	01/01/2023	Profit	10
33	Toast	01/01/2023	Profit	10
55	Friut	01/01/2023	Profit	20
22	Beans	01/01/2023	Volume Units	1
22	Beans	01/01/2023	Volume Units	1
22	Beans	01/01/2023	Volume Units	1
33	Toast	01/01/2023	Volume Units	1
33	Toast	01/01/2023	Volume Units	1
55	Fruit	01/01/2023	Volume Units	1
22	Beans	01/02/2023	Profit	5
22	Beans	01/02/2023	Profit	5
22	Beans	01/02/2023	Profit	5
33	Toast	01/02/2023	Profit	10
33	Toast	01/02/2023	Profit	10
55	Fruit	01/02/2023	Profit	20
22	Beans	01/02/2023	Volume Units	1
22	Beans	01/02/2023	Volume Units	1
22	Beans	01/02/2023	Volume Units	1
33	Toast	01/02/2023	Volume Units	1
33	Toast	01/02/2023	Volume Units	1
55	Fruit	01/02/2023	Volume Units	1

this is what I want (i.e. beans only return 1 per month as there is a distinct count performed on the product UID but the others are just a sum of their quantity

Product Group	Period	Required result
Beans	01/01/2023	1
Toast	01/01/2023	2
Fruit	01/01/2023	1
Beans	01/02/2023	1
Toast	01/02/2023	2
Fruit	01/02/2023	1

I have tried the following as a calculated column 

//
Value (Qty adj) =

IF(

    fact[Attribute] = "Volume Units"

    &&

        (fact[Product Group] = "Beans"

       Distinctcount(Product UID)

    ,

fact_combined[Value])

//

followed by a measure;

CALCULATE(sumx( fact, Value (Qty adj) , fact_combined[Attribute] = "Volume (Units)")

but what is returned in the calculated column in a count of every time that Product UID appears in the whole fact table (I have more months) so I get massive figures 

I'm certain a calculated column isn't the way to go now but not sure how to approach this 

Many thanks

James

[lbendlin]