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

 

 Capture1.PNGOriginal 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

 

Capture2.PNGDataset 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

Capture3.PNGGroup - 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

 

Capture4.PNGGroup = 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.

 

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
ar_46 Frequent Visitor
Frequent Visitor

Re: Aggregate by more than one column with added filter

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 Super Contributor
Super Contributor

Re: Aggregate by more than one column with added filter

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] )
    )
)

33.PNG

 

Thanks,
Xi Jin.

3 REPLIES 3
v-xjiin-msft Super Contributor
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
ar_46 Frequent Visitor
Frequent Visitor

Re: Aggregate by more than one column with added filter

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 Super Contributor
Super Contributor

Re: Aggregate by more than one column with added filter

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] )
    )
)

33.PNG

 

Thanks,
Xi Jin.