topic Re: Percentage of total (context and model) in DAX Commands and Tips

Percentage of total (context and model)

jesus_rc — Sun, 19 Mar 2023 17:54:13 GMT

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.

Re: Percentage of total (context and model)

puneetvijwani — Sun, 19 Mar 2023 18:40:18 GMT

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

Re: Percentage of total (context and model)

jesus_rc — Tue, 21 Mar 2023 23:46:26 GMT

Hi puneetvijwani ,

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

It worked with: