Average of sum of total dynamically

[Anonymous]

Hi,

I have the matrix below:

I need to create a measure that average the line Total dyamically. (I will use this measure as a line over bar chart)

For example. sum all the totals and divide by the numbers of years that I want (i.e sum from 2010 to 2015 and divide by 5 or sum the totals from 2010 to 2012 and divide by 2)

Is this possible creating some parameter?

Thank you

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

First create a parameter table as below,which is for dynamic selection:

Slicer table = GENERATESERIES(1,12,1)

Then create  a  measure as below:

Measure = IF(
    NOT(ISFILTERED('Slicer table'[Value])),CALCULATE(SUM('Table'[Value])),
    IF(ISINSCOPE('Table'[Country]),CALCULATE(SUM('Table'[Value])),
 var _sum=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Year]>=MAX('Table'[Year])-SELECTEDVALUE('Slicer table'[Value])&&'Table'[Year]<=MAX('Table'[Year])))
 Return
 DIVIDE(_sum,SELECTEDVALUE('Slicer table'[Value]))))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

[amitchandak]

@Anonymous , Try a measure like

AverageX(summarize(Table, Table[City], Table[Year], "_1",[measure]) ,[measure])

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.