Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Solved! Go to 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
@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
User | Count |
---|---|
97 | |
87 | |
78 | |
74 | |
69 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |