Create Field Parameter Calculated by Column

[Jonjon_99]

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

[johnyip · Accepted solution]

@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 · Accepted solution]

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]

@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]

can you help me do this with my file. i cant seem to make it work.

https://drive.google.com/drive/folders/1i7N8YWWwMvZHEU08rxWIRDBsaC_kz6sg?usp=sharing

[johnyip · Accepted solution]

@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]

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 · Accepted solution]

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]

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 · Accepted solution]

@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_99]

Thanks again John! 🙏🏼☺️

[Jonjon_99]

appreciate your help! 🙂

[Jonjon_99]

it works! thank you! 🙂 appreciate your help!!!

[Jonjon_99]

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