cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Grob2
Helper II
Helper II

Sum result from a grouping by category

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

7 REPLIES 7
Grob2
Helper II
Helper II

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
Not applicable

 

// 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
Not applicable

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

Thanks.

Best
D
Anonymous
Not applicable

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
Community Champion
Community Champion

Hi @Grob2 

try a measure

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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
Community Champion
Community Champion

@Grob2 

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))

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors