cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

sum of average after groupby

I have data as below. There are multiple other columns on the sides but these are the ones I'm interested in.

MonthCategoryValue
1B8
1A3
2A3
2A3
3A3
3A3
3A3

 

I am trying to create a measure in a bar chart with the month on the x-axis. For the y-axis I want to create a measure that groups the values based on the month and category, but takes the sum per each month.

 

So if I have a slicer to look at category A, then for the month 1 I want the bar to 3 units high. Month 2 should have the bar 6 units high. Month 3 should have it 9 units high. Can I get help for this?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

hey @goalie

 

maybe this measure is what you are looking for:

Measure = 
var t = 
    VALUES(
        'Table'[Category]
    )
return
SUMX(
    t
    ,
    var __m = MIN( 'Table'[Month] ) 
    return
    SUMX(
        FILTER( ALL( 'Table'[Month] ) , 'Table'[Month] <= __m )
        , CALCULATE( AVERAGE( 'Table'[Value] ) ) --average to honor the multiple values of category / month in the sample data
    )
)

This allows to create this visual:

image.png

Hopefully, this is what you are looking for.

If not, create a pbix file that contains sample data, reflects your data model, and also contains any measures that you already created.

Upload the pbix to onedrive or dropbox and share the link. If you are using an xlsx to create the sample data, share the xlsx as well.

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

9 REPLIES 9
Highlighted
Super User II
Super User II

Hey @goalie_ ,

 

not sure but I assume this measure provides what you are looking for:

 

Measure = 
AVERAGEX(
    VALUES( 'Table'[Category] )
    , CALCULATE( SUM( 'Table'[Value] ) )
) 

 

This allows to create this visualization:

image.png

And this is the result if no category is filtered
image.png

 

Hopefully, this is what you are looking for.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Highlighted

@TomMartens  almost. However, if you do something like this:(extra value in month 2, and 1 less in month 3)

MonthCategoryValue
1B8
1A3
2A3
2A3
2A3
3A3
3A3

 

you'll get a graph where it has for 9 for month 2 and 6 for month 3. I still need it to be 6 for month 2, 9 for month 3

Highlighted

Hey @goalie_ ,

 

please explain how

6 is computed for month 2 and

9 is computed for month 3

based on the data you provided.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Highlighted

Month 2 being 6 and Month 3 being 9 are predetermined values. The values column are goals that have been set. They are the accumulation of the values  month to month. The values come from a left join from another table. It's possible for a month to be a different value. For example,

MonthCategoryValue
1B8
1A3
2A4
2A4
2A4
3A3
3A3

 

When Category A is selected. I'd want the bar for month 1 to be 3, month 2 to be 7, and month 3 to be 10. When all categories are selected, I'd want month 1 to be 11, month 2 to be 7, and month 3 to be 10.

 

Hope that clarifies.

Highlighted

Hey @goalie_ ,

 

basically this clarifies nothing, from the table you provided it's still not clear how 7 is calculated for category A in Month 2

 

I recommend to create a second table that contains the predetermined values a single row for category/month.

 

Then create additional tables for

  • categories
  • months 

Relate the tables accordingly (https://docs.microsoft.com/en-us/learn/modules/model-data-power-bi/)

 

Then you can create mesures and/or calculated columns to populate your chart.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Highlighted

Hi @TomMartens  let's try this again.

 

Month 1 is 3. 

Month 2 is 4. the accumulation of month 1 and month 2 is 3+4 = 7

Month 3 is 3. the accumulation of months 1-3 is 3+4+3=10.

Highlighted

Hey,

 

I understand that, but nevertheless this does not explain why the accumulation of Month 2 is still 7, and does not honor the existence in of category B in month 1.

 

I'm wondering if this could make the rule for the accumulation:

Just accumulate the categories in all the preceding months.

 

I'm wondering what would be if there is a category B in Month 3, but not in Month 2 in these cases

  • category B is selected
  • no category is selected

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Highlighted

The value for each month is just a goal for that category. For each category there are subcategories. I simplified down my intial case to use an example to create a measure. 

 

If there is a month 3 in category B, it should be the sum of category B and A (in month 3) if no category is selected(because no selection = select all in a slicer). If only category B is selected, then it'll show the months for category B. 

 

I don't know the proper DAX to create the measure. 

Highlighted

hey @goalie

 

maybe this measure is what you are looking for:

Measure = 
var t = 
    VALUES(
        'Table'[Category]
    )
return
SUMX(
    t
    ,
    var __m = MIN( 'Table'[Month] ) 
    return
    SUMX(
        FILTER( ALL( 'Table'[Month] ) , 'Table'[Month] <= __m )
        , CALCULATE( AVERAGE( 'Table'[Value] ) ) --average to honor the multiple values of category / month in the sample data
    )
)

This allows to create this visual:

image.png

Hopefully, this is what you are looking for.

If not, create a pbix file that contains sample data, reflects your data model, and also contains any measures that you already created.

Upload the pbix to onedrive or dropbox and share the link. If you are using an xlsx to create the sample data, share the xlsx as well.

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors