Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ar_46
Regular 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 DatasetOriginal 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 ALLDataset 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 = SelectedGroup - 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 = SelectedGroup = 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

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.

 

 

 

View solution in original post

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.

View solution in original post

3 REPLIES 3
v-xjiin-msft
Solution Sage
Solution Sage

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.

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.

 

 

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.