Forum Discussion

Jonjon_99's avatar
Jonjon_99
Helper I
3 years ago
Solved

Create Field Parameter Calculated by Column

Hi, i am trying to create a filter that will help me toggle between Sales, Units, and percentage of Units/Sales of column total. i made field parameter but the calculation of percentage is incorrect...
  • johnyip's avatar
    johnyip
    3 years ago

    Jonjon_99 

    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.

  • johnyip's avatar
    johnyip
    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())
    )
  • johnyip's avatar
    johnyip
    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())
    ))