Forum Discussion
Aggregating Results From Multiple Variables Using SWITCH...Final Total Not Displaying?
Hi all - I'm working on a measure to be able to dynamically calculate results based on certain regions. I've already accomplished this for 5-6 other measures with similar complexity in the code ...
You can write measures that behave differently when rolled up using functions like HASONEVALUE, ISINSCOPE, ISFILTERED.
For example,
Distributable Margin = VAR Regional_Margin = DIVIDE ( ... ) VAR GLOBAL_Margin = CALCULATE ( ... ) VAR Regional_Result = SWITCH ( ... ) VAR GLOBAL_Result = IF ( ... ) VAR Final_Result = IF ( ISINSCOPE ( Data[Region] ), Regional_Result, GLOBAL_Result ) RETURN Final_Result
Hi Alexis - Sorry for not responding earlier with a thank you when you answered my follow up question yesterday, but thank you!! This was extermely helpful and gave me a simpler way to think through the logic of how I'm approaching writing variables in DAX. I'm just getting started with this aspect of BI, so it's certainly helpful. Also, your code from earlier regarding the Distributable Margin amounts in $ was great, but I made some slight changes to the revenue variable so it was being properly calculated within the SUMX RETURN...see below. 🙂
Distributable Profit $ - Up to 20% Margin Tier =
VAR Global_Margin =
CALCULATE (
DIVIDE (
[Profit/(Loss)],
[Revenue],
0
),
ALL ( 'Revenue & Costs Data'[Region] )
)
VAR Cutoff = [Margin Measure]
RETURN
CALCULATE (
SUMX (
VALUES ( 'Revenue & Costs Data'[Region] ),
VAR CurrRegion = 'Revenue & Costs Data'[Region]
VAR Margin =
DIVIDE (
[Profit/(Loss)],
[Revenue],
0
)
VAR Revenue = 'Financial Measures'[Revenue]
VAR Target_Variance =
DIVIDE (
[Target Profit Variance],
[Revenue],
0
)
VAR DistMargin = [Distributable Margin - Up to 20% Margin Tier Updated DAX Logic]
RETURN
IF (
Global_Margin > Cutoff
&& Target_Variance > 0,
Revenue * DistMargin
)
)
)Nice. I'm glad to see you got it working.