Forum Discussion
Create Field Parameter Calculated by Column
- 3 years ago
Specifically for your sample pibx, you could make changes to [%Total Units] as follows.
%Total Units = VAR CurrentMonth = MAX('Date'[MonthName]) RETURN DIVIDE(SUM(SalesFact[Units]), CALCULATE(SUM(SalesFact[Units]),FILTER(ALLSELECTED(SalesFact),RELATED('Date'[MonthName])=CurrentMonth)))Or you can create another measure with this definition if you wish to retain the original measure for some other use.
In this case, change the line of 'Sales Type'[Sales Type] regarding %Total Units, to pointing the new mesaure you have created.
- 3 years ago
VAR CurrentMonth = MAX('Date'[MonthName]) VAR CurrentYear = MAX('Date'[Year]) RETURN IF(OR(ISINSCOPE('Date'[MonthName]),ISINSCOPE('Date'[Month])), DIVIDE(SUM(SalesFact[Units]), CALCULATE(SUM(SalesFact[Units]),FILTER(ALLSELECTED(SalesFact),RELATED('Date'[MonthName])=CurrentMonth))), IF(ISINSCOPE('Date'[Year]), DIVIDE(SUM(SalesFact[Units]), CALCULATE(SUM(SalesFact[Units]),FILTER(ALLSELECTED(SalesFact),RELATED('Date'[Year])=CurrentYear))), BLANK()) ) - 2 years ago
Jonjon_99 You have misplaced parenthesis. The below should be working.
%Total Units = VAR CurrentMonth = MAX('Date'[MonthName]) VAR CurrentYear = MAX('Date'[Year]) VAR CurrentQuarter = MAX('Date'[Quarter]) RETURN IF(OR(ISINSCOPE('Date'[MonthName]),ISINSCOPE('Date'[Month])),DIVIDE(SUM(SalesFact[Units]), CALCULATE(SUM(SalesFact[Units]),FILTER(ALLSELECTED(SalesFact),RELATED('Date'[MonthName])=CurrentMonth))), IF(ISINSCOPE('Date'[Year]), DIVIDE(SUM(SalesFact[Units]), CALCULATE(SUM(SalesFact[Units]),FILTER(ALLSELECTED(SalesFact),RELATED('Date'[Year])=CurrentYear))), IF(ISINSCOPE('Date'[Quarter]), DIVIDE(SUM(SalesFact[Units]), CALCULATE(SUM(SalesFact[Units]),FILTER(ALLSELECTED(SalesFact),RELATED('Date'[Quarter])=CurrentQuarter))), BLANK()) ))
Specifically for your sample pibx, you could make changes to [%Total Units] as follows.
%Total Units =
VAR CurrentMonth = MAX('Date'[MonthName])
RETURN
DIVIDE(SUM(SalesFact[Units]),
CALCULATE(SUM(SalesFact[Units]),FILTER(ALLSELECTED(SalesFact),RELATED('Date'[MonthName])=CurrentMonth)))
Or you can create another measure with this definition if you wish to retain the original measure for some other use.
In this case, change the line of 'Sales Type'[Sales Type] regarding %Total Units, to pointing the new mesaure you have created.
it works! thank you! 🙂 appreciate your help!!!