cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ElliotP Member
Member

Visual Level Filter a Measure

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ElliotP Member
Member

Re: Visual Level Filter a Measure

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.

View solution in original post

8 REPLIES 8
Microsoft v-huizhn-msft
Microsoft

Re: Visual Level Filter a Measure

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. 

1.PNG

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

2.PNG

>>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 Member
Member

Re: Visual Level Filter a Measure

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?

Microsoft v-huizhn-msft
Microsoft

Re: Visual Level Filter a Measure

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 Member
Member

Re: Visual Level Filter a Measure

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)
Microsoft v-huizhn-msft
Microsoft

Re: Visual Level Filter a Measure

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 Member
Member

Re: Visual Level Filter a Measure

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

Microsoft v-huizhn-msft
Microsoft

Re: Visual Level Filter a Measure

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 Member
Member

Re: Visual Level Filter a Measure

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.

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors