topic Re: Sum result from a grouping by category in DAX Commands and Tips

Sum result from a grouping by category

Anonymous — Thu, 05 Mar 2020 16:29:44 GMT

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 2

- Category 2

- Product 1

- Product 3

- Category 3

- Product 1

- 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

Re: Sum result from a grouping by category

az38 — Thu, 05 Mar 2020 16:50:28 GMT

Hi Anonymous

try a measure

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

Re: Sum result from a grouping by category

Anonymous — Thu, 05 Mar 2020 17:00:58 GMT

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

Re: Sum result from a grouping by category

az38 — Thu, 05 Mar 2020 17:06:36 GMT

Anonymous

try

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

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

Re: Sum result from a grouping by category

Anonymous — Sat, 07 Mar 2020 22:36:38 GMT

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

Re: Sum result from a grouping by category

Anonymous — Mon, 09 Mar 2020 08:39:33 GMT

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

Re: Sum result from a grouping by category

Anonymous — Mon, 09 Mar 2020 12:49:04 GMT

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

Re: Sum result from a grouping by category

Anonymous — Mon, 16 Mar 2020 10:05:08 GMT

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

Thanks.

Best
D