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

 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 ?

Thanks in advance.

[ar_46 · Accepted solution]

HI @v-xjiin-msft,

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.

[v-xjiin-msft]

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.

[ar_46 · Accepted solution]

HI @v-xjiin-msft,

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.

[v-xjiin-msft · Accepted solution]

Hi @ar_46,

I'm glad to hear that you have resolved your issue. And your solution is great. 

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.