Count per group of summarizecolumns

[Mikeysw]

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

[Mikeysw]

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!

[Fowmy]

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

)