Group By and Summarize

[akshay2204]

Hi,

I need solution for the following .

I need a distinct count of customers who are relarted to the major group. When We ran a query in Sql we got the expected result. We are facing issue in creating the dax query for this. Please help me. 

Thanks in advance

[akshay2204]

Hi @tamerj1  I checked this but still throwing an error for value conversion. Basically Year ID and Month ID will be used in slicers. we only need customer count and Major group

[tamerj1]

@akshay2204 

Calculated tables cannot interact with the filter context. This has to be a measure. In this case the year and moth slicers will filter the table automatically and no need to to be included in the dax filter. 

You need to create a MajorGroupCount table. This would be a single column disconnected table that includes integers ranging from 1 to the number of unique majour groups. Can be manually inserted or created using power query or dax. 
A dax exsmple would be

MajorGroupCount =

SELECTCOLUMN (
GENERATESERIES ( 1, DISTINCTCOUNT ( vwSalesData[MajorGroupID] ), 1 ),

"Count", [Value]
)

Place MajorGroupCount[Count] in a table visual along with the following measure 

CountMeasure =
SUMX (
VALUES ( MajorGroupCount[Count] ),
COUNTROWS (
FILTER (
SUMMARIZE (
vwSalesData,
vwSalesData[CustomerCode],
"MajorGroupCount", DISTINCTCOUNT ( vwSalesData[MajorGroupID] )
),
[MajorGroupCount] = MajorGroupCount[Count]
)
)
)

[tamerj1]

Hi @akshay2204 

please try

NewTable =
GROUPBY (
SUMMARIZE (
FILTER ( vwSalesData, vwSalesData[YearID] = 22 && vwSalesData[MonthID] = 4 ),
vwSalesData[CustomerCode],
"MajorGroupCount", DISTINCTCOUNT ( vwSalesData[MajorGroupID] )
),
[MajorGroupCount],
"#ofCustomers", COUNTX ( CURRENTGROUP (), 1 )
)