cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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!