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()) ))
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())
)hi John, i try adding Quarter but it is not working, can you please check this if this is correct.
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())
)
- johnyip2 years agoSolution Sage
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()) ))- Jonjon_992 years agoHelper I
Thanks again John! 🙏🏼☺️