cancel
Showing results for
Did you mean:
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

Original Dataset

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

• Group A - Color
• Group A- Black & white
• Group B- Color
• Group B- 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]))

Results

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

1. If I have ALL, ALL , ALL - for group, color and month, the sum and average are the same across group/color combo [Group A Average: 661 (Black), 633 (Color)] in the table

Dataset with ALLALL ALL

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

[Group A Average: 633 (Color)] in the table

Group - Sselected and Color = Selected

The problem is :

3. 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.[Group A: diffrent Average in every row ] in the table

Group = Selected and Month = Selected

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Frequent Visitor

## Re: Aggregate by more than one column with added filter

Thank you for trying to help me. I was finally able to resolve my issue as specified below but can you please help optimize the same ?

Basically, my problem was that I have 3 filters with a group by on two of them.

I was having a hard time making the Group By filters work with the 3rd filter while computing average.

The problem occurs when I try to print to print the average at the row level.

Grouping by : Model Group and Print Mode

Another filter is : Month/Year

I was able to resolve this using this using the below DAX measure.

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

Any help in ptimizing would be very helpful.

Super Contributor

## Re: Aggregate by more than one column with added filter

Hi @ar_46,

Then here's another method to get the average value without using GROUPBY(). It is hard to say which one is better, you can just make a reference.

AvgValue without GroupBy =
CALCULATE (
AVERAGE ( TestData[Print Count] ),
FILTER (
ALLSELECTED ( TestData ),
TestData[Model Group] = MAX ( TestData[Model Group] )
&& TestData[Print Mode] = MAX ( TestData[Print Mode] )
)
)

Thanks,
Xi Jin.

3 REPLIES 3
Super Contributor

## Re: Aggregate by more than one column with added filter

Hi @ar_46,

Could you please share us your pbix file with One Drive or Google Drive if possible? It'll help us understand your requirement more clearly.

Thanks,
Xi Jin.

Highlighted
Frequent Visitor

## Re: Aggregate by more than one column with added filter

Thank you for trying to help me. I was finally able to resolve my issue as specified below but can you please help optimize the same ?

Basically, my problem was that I have 3 filters with a group by on two of them.

I was having a hard time making the Group By filters work with the 3rd filter while computing average.

The problem occurs when I try to print to print the average at the row level.

Grouping by : Model Group and Print Mode

Another filter is : Month/Year

I was able to resolve this using this using the below DAX measure.

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

Any help in ptimizing would be very helpful.

Super Contributor

## Re: Aggregate by more than one column with added filter

Hi @ar_46,

Then here's another method to get the average value without using GROUPBY(). It is hard to say which one is better, you can just make a reference.

AvgValue without GroupBy =
CALCULATE (
AVERAGE ( TestData[Print Count] ),
FILTER (
ALLSELECTED ( TestData ),
TestData[Model Group] = MAX ( TestData[Model Group] )
&& TestData[Print Mode] = MAX ( TestData[Print Mode] )
)
)

Thanks,
Xi Jin.