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 since it calculates the whole chart instead of per column total.
can anyone help me with this one? thanks in advance!

 


its working in Units.

 

but not working in %Total Units/Sales, i want this to be calculated in percentage by column. 😞 


this is the DAX i used.

%Total Units = DIVIDE(SUM(SalesFact[Units]),
                        CALCULATE(SUM(SalesFact[Units]),ALLSELECTED(SalesFact)))

 

seeking for your help. 🙂

 

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

     

     

11 Replies

  • 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! 🙂

      • 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.