Aggregate by more than one column with added filter

[ar_46]

I have a data set like this with 5 columns:

Group,  Color, Serial Number, Month, count

I have to find the SUM and AVERAGE , grouped by Group and Color : 

• Group A - Color
• Group A- Black & white
• Group B- Color
• Group - Black & White

The table BI visualization needs to show:

Group , Color , Serial Number, Month, count, Calculated SUM, Calculated AVERAGE

I need the following filters on the top

• Group Filter
• Color Filter
• Month Filter

My current measures:

SumValue = CALCULATE(SUM(TestData[Print Count]),ALLSELECTED(TestData),GROUPBY(TestData,TestData[Model Group],TestData[Print Mode]))

AvgValue = CALCULATE(AVERAGE(TestData[Print Count]),ALLSELECTED(TestData),GROUPBY(TestData,TestData[Model Group],TestData[Print Mode]))

The problem is :

When I display the SUM and AVERAGE in the table view with the rest of the fields:

If I have ALL, ALL , ALL - for group, color and month, the sum and average are the same across group/color combo

If I have group-Selected, color = SELECTED and MONTH = ALL, , the sum and average are the same across group/color combo

If I have either group or color selected with MONTH selection, then the sum and averages are no longer the same in the group/color combo.

How do I make the group / print filter work with the Month filter to show the same mean for every row in the table ?

PS: I need the mean at the table level .

Am I missing something ?

[v-jiascu-msft]

Hi @ar_46,

Can you share a sample and the expected result please? DAX functions will evaluate their contexts. We dont' need to group them in the formula. Maybe the result could be as following.

Group   Color   SUM(count)   AVERAGE(count)

Best Regards,

Dale