Visual Level Filter a Measure

[ElliotP]

Evening,

I have a bar chart with the axis as a date and the value as a measure for the Sum of Net Sales in the past week.

When I add a visual level filter of a created conditional column (text value) the graph goes blank. It works when I use the Net Sales value from the column, so I feel it's an issue with my measure.

P0WNetSales = 
VAR CurrentYear =
MAX ( 'ExtendedCalendar'[Year] )
VAR CurrentWeekNum =
CALCULATE (
MAX ( 'ExtendedCalendar'[WeekNum] ) - 0, FILTER(ALL( 'ExtendedCalendar' ), 'ExtendedCalendar'[Year] = CurrentYear )
)
RETURN

CALCULATE (
SUM('itemdetailsdogfood$'[Net Sales]),

'ExtendedCalendar'[Year] = CurrentYear,
'ExtendedCalendar'[WeekNum]
= CurrentWeekNum
)

Any thoughts? I'm completly out of ideas.

[v-huizhn-msft]

Hi @ElliotP,

For your given measure, there is no syntax error. But the measure calculates the sum net of max week for current year. So I try to reproduce using my sample data table, I post solution to calculate the Sum of Net Sales in the past week. 



Create a measure like yours. You want to sum net in one week, you'd better add a filter.(I use ALLEXCEPT in my measure)

Your measure = 
VAR CurrentYear=MAX('Calendar'[Year])
VAR CurrentWeekNum=CALCULATE(MAX('Calendar'[weeknum])-0,FILTER(ALL('Calendar'),'Calendar'[Year]=CurrentYear))
RETURN
CALCULATE(SUM(Net[Net]),Net[Year]=CurrentYear,Net[WeekNum]=CurrentWeekNum,ALLEXCEPT(Net,Net[WeekNum]))

I post my solution to achieve your requriement. Create calculated columns using the following formulas.

one week = CALCULATE(SUM(Net[Net]),ALLEXCEPT(Net,Net[WeekNum]))

Rank = RANKX(Net,Net[WeekNum],,ASC,Dense)

Last week = LOOKUPVALUE(Net[one week],Net[Rank],Net[Rank]-1)

Finnaly, I create a table to display the result shown in screenshot below. The last week column displays all sum net for last week. While your measure column shows the sum net for max weeknum(in my calendar table, the max year is 2017, the last weeknum is 6).



>>When I add a visual level filter of a created conditional column (text value) the graph goes blank. 

Based on your discription, I am unable to reproduce conditional column, could you please post a screenshot or share your .pbix file for further analysis? Thanks a lot.

Best Regards,
Angelia

[ElliotP]

Thanks so much for the response; it really helped. In terms of the visual filter, It seems to be working but I'm not sure what I changed along the way as I was re-adding and creating the tables again.

I probably should create another thread for my next question resulting from this but I'll type it out briefly.

I want to calculate the percentage change between weeks of the sum of the sales for that week. I've done this and I have the correct figure; but when i attempt to plot it on a line chart over time, it shows all available week numbers as opposed to just Week 5 = 50% as well as I can't work out how to have the line on the graph then move up and down depending upon the week.

I know it's an issue with the measure and this might be better suited to approaching it from another manner to show this. Any thoughts or ideas?

[v-huizhn-msft]

Hi @ElliotP,

You have calculated the percentage change and got the correct figure, could you please share a screenshot? In line chart, you add the weeknum as axis level, it display uncorrectly? Please share more details, thanks very much.

Best Regards,
Angelia

[ElliotP]

Here's the screenshot:

https://gyazo.com/e86e45fe581b14696abfe7c9874cf654

Using this code to calculate last week's net sales:

P1WNetSales = 
VAR CurrentYear =
MAX ( 'ExtendedCalendar'[Year] )
VAR CurrentWeekNum =
CALCULATE (
MAX ( 'ExtendedCalendar'[WeekNum] ) - 1, FILTER(ALL( 'ExtendedCalendar' ), 'ExtendedCalendar'[Year] = CurrentYear )
)
RETURN

CALCULATE (
SUM('itemdetailsdogfood$'[Net Sales]),

'ExtendedCalendar'[Year] = CurrentYear,
'ExtendedCalendar'[WeekNum]
= CurrentWeekNum
)

And this code to calculate the percentage change between weeks:

P1-2WNetSales%Change = DIVIDE(([P1WNetSales]-[P2WNetSales]), [P2WNetSales],0)

[v-huizhn-msft]

Hi @ElliotP,

Please create a measure and a card used to display the measure value using the following formula. Create a table, please add the weeknum as row level, CurrentWeekNum measure as value level, please see the if CurrentWeekNum measure is constant.

CurrentWeekNum=CALCULATE (
MAX ( 'ExtendedCalendar'[WeekNum] ) - 1, FILTER(ALL( 'ExtendedCalendar' ), 'ExtendedCalendar'[Year] = CurrentYear )
)

Or could you please share your .pbix file for further analysis?

Best Regards,
Angelia

[ElliotP]

Thank you, I sorted it out earlier today. It had to do the filtering of the relationships between tables (not sure how it got changed, but oh well).

[v-huizhn-msft]

Hi @ElliotP，

Do you have resolved your issue? If it does, please mark helpful reply or share your own solution, which will help more people.

Best Regards,
Angelia

[ElliotP · Accepted solution]

It had to do with the filtering between the tables. The cross directional filter between my date table and data table was set to Both as opposed to single and this was causing issues with my filtering from values in my data table.