Sum result from a grouping by category

[Anonymous]

I have a hierarchy Category > Product. A product can be once, twice, etc. or zero time in a category.

Like that:

- Category 1

  - Product 1

  - Product 1

  - Product 2

- Category 2

  - Product 1

  - Product 3

- Category 3

  - Product 1

  - Product 3

  - Product 3

  - ...

I want to know if a Product is in several Category. I wanted to distinctcount products by category then sum the result.

But it seems like I do not know how to use GROUPBY()

Do you know how to handle it?

Thank you

[Anonymous]

Thank you for all these answers!

How could I get the total number of multi-category products.

Because, when I display the product column and the DAX mesure, I can know if a product is multi-category or not.

But when I put it in a card visual, I only get 1.

Is it possible to sum the distinct count of product by category for each product in a measure?

Regards

[Anonymous]

// For each product, this measure returns
// the total number of categories the product
// belongs to (regardless of how many categories
// are visible). Bear in mind that the bridge table
// ProductCategory should be hidden and slicing
// cannot be done on it. It's only an auxiliary
// table that associates products with their
// categories.
[# Total Categories] =
var __oneProdVisible = HASONEVALUE( Product[ProductId] )
var __result =
	CALCULATE(
		SUMX(
			ProductCategory,
			// If the type of the field below is int
			// then you don't have to multiply by 1,
			// but if it's boolean, you have to.
			ProductCategory[Unit] * 1
		),
		ALL( Category )
	)
return
	if( __oneProdVisible, __result )

// This tells you how many products in the current
// context are multi-cat products. If you want to
// see 0 instead of BLANK, you can add 0 to
// the COUNTROWS function.
[# Multi-Cat Products] =
	COUNTROWS(
		FILTER(
			VALUES( Product[ProductId] ),
			[# Total Categories] > 1
		)
	)

Best

D

[Anonymous]

Please mark the answer that's the solution as the solution via the 'Accept as Solution' button below the post.

Thanks.

Best
D

[Anonymous]

If a product can be in several categories, then there must be a bridge table that associates a product to categories. Say that you've got this setup: Product [1:*] ProductCategory [*:1] Category. You say that each product is associated with every category but there's a field in ProductCategory (say its name is Present) that has 1 in it when the category applies to the product and 0 if not. You want to know for each product if it's in at least 2 categories. So this would be a calculated column.

Here's the formula:

[In At Least 2 Categories] = // calc column
sumx(
    relatedtable( ProductCategory ),
    ProductCategory[Present] * 1
) > 1

If you want a measure that will work only for individual products...

[In At Least 2 Categories] =
var __oneProdVisible = HASONEVALUE( Product[ProductId] )
var __result =
	SUMX(
		ProductCategory,
		// If the type of the field below is int
		// then you don't have to multiply by 1,
		// but if it's boolean, you have to.
		ProductCategory[Unit] * 1
	) > 1
return
	if( __oneProdVisible, __result )

Best

D

[az38]

Hi @Anonymous 

try a measure

Measure = CALCULATE(DISTINCTCOUNT(Table[Category]), ALLEXCEPT(Table, Table[Product]))

[Anonymous]

Thanks for this answer!

What I have found (because it is in DirectQuery and difficult to see every lines) is that each Product is present in each Category but with a unit of 0 if they are not present.

How could I add a filter on object[unit] > 0? (it does not work like that)

Thanks

[az38]

@Anonymous 

try

Measure = CALCULATE(DISTINCTCOUNT(Table[Category]), ALLEXCEPT(Table, Table[Product]), Table[Unit]>0)

or

Measure = CALCULATE(DISTINCTCOUNT(Table[Category]), FILTER(ALL(Table), Table[Product]=SELECTEDVALUE(Table[Product]) && Table[Unit]>0))