topic Re: Count per group of summarizecolumns in DAX Commands and Tips

Count per group of summarizecolumns

Mikeysw — Mon, 15 Jan 2024 19:01:27 GMT

Hi,

When using Summarizecolumns, I would like to get count of how manu times each child group appears.

For example, I have this DAX query

SUMMARIZECOLUMNS (

'Date'[Calendar Year],

'Product'[Color],

"Amount", [Sales Amount],

"Quantity",Sum(Sales[Quantity]))

I would like then to count the result and display how many times each color appears for each year.

I tried with groupby and currentgroup but I get double results as I have two measures.

Thank you!

Mike

Re: Count per group of summarizecolumns

Fowmy — Mon, 15 Jan 2024 19:25:33 GMT

Mikeysw

You are grouping by Year and Color, then calculate two meaures so you should get each color appearing once. Here is the result:

EVALUATE

VAR __t1 =

SUMMARIZECOLUMNS (

'Date'[Year],

'Product'[Color],

"Amount", [Sales Amount],

"Quantity",Sum(Sales[Quantity])

)

RETURN

GROUPBY(

__t1,

'Date'[Year],

'Product'[Color],

"ColorCount" , SUMX(CURRENTGROUP(), 1 )

)

Re: Count per group of summarizecolumns

Mikeysw — Tue, 16 Jan 2024 13:14:28 GMT

You are correct, thank you!

However my case is a bit more complicated as I have query with 3 dimensions (Continent/CountryRegion/City) part of paginated report. I would like to highlight only those child groups where there is 1 item.

So I need to get a count of items per each parent group.

In this case, let's use different query, to be more exact.

EVALUATE
VAR __t1 =
SUMMARIZECOLUMNS (
Customer[Continent],
Customer[CountryRegion],
Customer[City],
"Amount", [Sales Amount]
)

RETURN
GROUPBY(
__t1,
Customer[Continent],
Customer[CountryRegion],
"Count" , countX(CURRENTGROUP(), Customer[CountryRegion] )
) ORDER BY Customer[Continent] DESC

Which brings me this

Result I am looking for is

And in a case where I have 3 dimensions
EVALUATE
VAR __t1 =
SUMMARIZECOLUMNS (
Customer[Continent],
Customer[CountryRegion],
Customer[City],
"Amount", [Sales Amount]
)

RETURN
GROUPBY(
__t1,
Customer[Continent],
Customer[CountryRegion],
Customer[City],
"Count" , countX(CURRENTGROUP(), Customer[City] )
) ORDER BY Customer[Continent] DESC

I get

But I would like to see

How should I approach this?
Thank you!