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 

I don't have your sample data, but the DAX should be something like this.

%Total Units = 
VAR CurrentMonth = MAX(SalesFact[Month])
RETURN
DIVIDE(SUM(SalesFact[Units]),
       CALCULATE(SUM(SalesFact[Units]),FILTER(ALLSELECTED(SalesFact),SalesFact[Month]=CurrentMonth)))
Jonjon_99's avatar
Jonjon_99
Helper I
3 years ago

hi John, thanks for your answer. will try it and will attached my pbix file if i cant still make it work.  thank you! 🙂