Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Measure - Average percent

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 o...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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

amitchandak's avatar
amitchandak
Super User
4 years ago

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