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...
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • 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())
))

     

     

johnyip's avatar
johnyip
Solution Sage
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.

Jonjon_99's avatar
Jonjon_99
Helper I
3 years ago

hi John. good day! seeking for your help again. 😄
is there a way you can add filtered by year in this report. 
i want to add additonal matrix by year.


 

  • johnyip's avatar
    johnyip
    Solution Sage
    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())
)
    • Jonjon_99's avatar
      Jonjon_99
      Helper I
      3 years ago

      appreciate your help! 🙂

    • Jonjon_99's avatar
      Jonjon_99
      Helper I
      2 years ago

      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())
      )

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