cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

average and max of grouped sum by two columns

I have a dataset with three columns as follows. i want to first grouped data by two columns day and bucket and sum over values and second calculate average and max of sums over buckets. I would appreciate if you can help me to figure this out by adding a measure to my table to calculate this.

 day bucket value 1/1/2023 1 10 1/1/2023 1 20 1/1/2023 2 5 1/1/2023 2 15 1/2/2023 1 30 1/2/2023 1 10 1/2/2023 2 25 1/2/2023 2 5 day bucket value 1/1/2023 1 30 1/1/2023 2 20 1/2/2023 1 40 1/2/2023 2 30 bucket value 1 35 2 25 bucket value 1 40 2 30
1 ACCEPTED SOLUTION
Super User

Can you try this measures?

1. Average

``````Average =
VAR _temp = SUMMARIZE(
Test_max_avg,
Test_max_avg[day],
Test_max_avg[bucket],
"@Value",SUM(Test_max_avg[value]))
VAR _result = AVERAGEX(_temp,[@Value])
RETURN _result``````

2. Max

``````Max =
VAR _temp = SUMMARIZE(
Test_max_avg,
Test_max_avg[day],
Test_max_avg[bucket],
"@Value",SUM(Test_max_avg[value]))
VAR _result = MAXX(_temp,[@Value])
RETURN _result``````

Result:

Thanks,

Arul

Proud to be a Super User!

4 REPLIES 4
Frequent Visitor

Thank you so much Arul, it works.

Frequent Visitor

I want to use bucket in x axis and average and max in y axis of a line chart.

Super User

Can you try this measures?

1. Average

``````Average =
VAR _temp = SUMMARIZE(
Test_max_avg,
Test_max_avg[day],
Test_max_avg[bucket],
"@Value",SUM(Test_max_avg[value]))
VAR _result = AVERAGEX(_temp,[@Value])
RETURN _result``````

2. Max

``````Max =
VAR _temp = SUMMARIZE(
Test_max_avg,
Test_max_avg[day],
Test_max_avg[bucket],
"@Value",SUM(Test_max_avg[value]))
VAR _result = MAXX(_temp,[@Value])
RETURN _result``````

Result:

Thanks,

Arul

Proud to be a Super User!

Super User

In which visualization you want to show this and what are the columns that you are going to use in the visualization?

Thanks,

Arul

Proud to be a Super User!

Announcements

Fabric certifications survey

Certification feedback opportunity for the community.

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors