Skip to main content
cancel
Showing results for 
Search instead 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

Reply
saeidrasti
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. 

daybucketvalue
1/1/2023110
1/1/2023120
1/1/202325
1/1/2023215
1/2/2023130
1/2/2023110
1/2/2023225
1/2/202325
   
daybucketvalue
1/1/2023130
1/1/2023220
1/2/2023140
1/2/2023230
   
   
bucketvalue 
135 
225 
   
bucketvalue 
140 
230 
1 ACCEPTED SOLUTION

@saeidrasti ,

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:

Arul_0-1685457185102.png

Thanks,

Arul

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


View solution in original post

4 REPLIES 4
saeidrasti
Frequent Visitor

Thank you so much Arul, it works. 

saeidrasti
Frequent Visitor

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

@saeidrasti ,

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:

Arul_0-1685457185102.png

Thanks,

Arul

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


Arul
Super User
Super User

@saeidrasti ,

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

Thanks,

Arul





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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