Percentage of total (context and model)

[jesus_rc]

Hi,

I am creating a measure to calculate a "% of total" taking in the context the date field, but it only works if the date field is in the same table as the other values, i.e., when I try to use the date field from my DimCalendar it won't work. What am I doing wrong? Is it the modelling or the DAX?

PBIX file avaliable in my OneDrive here.

This works:

% of year total = 
VAR amount = [Expenses]
VAR TOT =
CALCULATE(
    [Expenses],
    ALLEXCEPT(English, English[Year]) // English[Year] is a redundant calc col
)

RETURN
IF(
    SELECTEDVALUE(English[Year], "-") <> "-",
    DIVIDE (amount , TOT, "Error"),
    "Select a year"
)

This doesn't:

% of year total = 
VAR amount = [Expenses]
VAR TOT =
CALCULATE(
    [Expenses],
    ALLEXCEPT(DimCalendar, DimCalendar[Year]) // This won't work
)

RETURN
IF(
    SELECTEDVALUE(English[Year], "-") <> "-",
    DIVIDE (amount , TOT, "Error"),
    "Select a year"
)

Thanks in advance for any tip.

[jesus_rc · Accepted solution]

Hi @puneetvijwani ,

Thankss a lot for your support. You gave the insight I needed!

It worked with:

% of year total =

VAR amount = [Expenses]

VAR TOT =

CALCULATE(

    [Expenses],

   REMOVEFILTERS(English[Department) // instead of REMOVEFILTERS(DimCalendar).

)

RETURN

IF(

    SELECTEDVALUE(DimCalendar[Year], "-") <> "-",

    DIVIDE (amount , TOT, "Error"),

    "Select a year")

I wasn't familiar with REMOVEFILTERS, guess I'll be using it more from now on.

[puneetvijwani]

HI @jesus_rc  try uing the below DAX
And use the Year column from DIm calender table in your Filter visual Instead of english table's year ; let me know if that works 

% of year total =

VAR amount = [Expenses]

VAR TOT =

CALCULATE(

    [Expenses],

   REMOVEFILTERS(DimCalendar)

)

RETURN

IF(

    SELECTEDVALUE(DimCalendar[Year], "-") <> "-",

    DIVIDE (amount , TOT, "Error"),

    "Select a year")