cancel
Showing results for
Did you mean:
Highlighted
Helper I

## show weekly change average

Source table:

 ID Date WeekNum Pageview A Jul 27 2020 31 100 A Jul 23 2020 30 200 A Aug 4 2020 32 300 B Jul 23 2020 30 333 B Jul 27 2020 31 111 B Jul 27 2020 31 111

Then I created a dynamic table by summarize ():

 ID WeekNum Weekly total % difference vs last week A 30 200 0 A 31 100 -50% A 32 500 400% B 30 333 0 B 31 222 -33.3%

I was able to use Matrix table to show this, but I now want to use avg weekly % change to display with other chart visual like above, how could I extract the 'weekly% change' per ID?

 ID 31 % change 32 % change total (avg) A -50 400 175% B 0 -33.3% -33.3%

i.e. Is there any way to get avg % not using Matrix, and make the 'avg of weekly % change' usage in other chart?

 ID Avg of weekly % change A 175% B -33.3%
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IX

## Re: show weekly change average

@qucikq1 - Yes, you can do it all dynamically in variables in DAX. For example here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

You can use something like:

VAR __Table = ADDCOLUMNS(SUMMARIZE(....),"% change", <calc goes here>)

---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

3 REPLIES 3
Highlighted
Super User IX

## Re: show weekly change average

@qucikq1 , refer if my WOW blog can help

Proud to be a Super User!

Highlighted
Super User IX

## Re: show weekly change average

@qucikq1 - Yes, you can do it all dynamically in variables in DAX. For example here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

You can use something like:

VAR __Table = ADDCOLUMNS(SUMMARIZE(....),"% change", <calc goes here>)

---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Helper I

## I Re: show weekly change average

Thanks both, i eventually figured out my ans below:

AVG =
AVERAGEX (
SUMMARIZE (
'mytable',
'mytable'[ID],
'mytable'[% difference vs Last Week]
),
[% difference vs Last Week]
)

Announcements

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors