cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ar_46 Frequent Visitor
Frequent Visitor

Aggregate by more than one column with added filter

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 ?

 

 

 

 

1 REPLY 1
Community Support Team
Community Support Team

Re: Aggregate by more than one column with added filter

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.