Measure - Average percent

[Anonymous]

Hello Community -  I have a table, with Team names and month, and margin.  

Ideally, I need a table that can filtered at the year, quarter, and month level  (so dynamically will adjust dependin on what hierarchy level).    Secondly, I need the columns to come to a "total" average.    The row totals already do this...but the column "totals" do not.   If add the monthly amounts...and divide by 9 (months), the row totals work fine.    But if you add the columns up, they do not come to the correct "average".  

I am using the measure below, but in summmary, I simply want to be able to put a measure on this visual that gives me the average margin amounts, by Team, and by whatever date hierarchy is selected.   It should also dynamically adjust to whatever Teams are selected.    

Here is my main margin measure:   

Net Shipped Margin % = DIVIDE([Net Shipped Margin $], [Net Shipped Revenue],0)

And this one I made specifically to see the month...but ideally I need this to be dynamic to change with the date hiearchy. 

Monthly Average of Net Shipped Margin % = AVERAGEX(

SUMMARIZE('Dim_Date Table',

'Dim_Date Table'[Month Name],

"Average", DIVIDE([Net Shipped Margin $], [Net Shipped Revenue],0)

),

[Average]

)

[amitchandak · Accepted solution]

@Anonymous , You need to use isinscope and change measure in switch

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

Monthly Average of Net Shipped Margin % =

Switch(true() ,

isinscope('Dim_Date Table'[Month Name]),

AVERAGEX(
SUMMARIZE('Dim_Date Table',
'Dim_Date Table'[Month Name],
"Average", DIVIDE([Net Shipped Margin $], [Net Shipped Revenue],0)
),
[Average]
)

,

AVERAGEX(
SUMMARIZE('Dim_Date Table',
'Dim_Date Table'[Date],
"Average", DIVIDE([Net Shipped Margin $], [Net Shipped Revenue],0)
),
[Average]
)

)

add conditions as per need