Forum Discussion

ElliotP's avatar
ElliotP
Post Prodigy
9 years ago
Solved

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.

Need Help
Tips and Tricks
  • ElliotP's avatar
    ElliotP
    9 years ago

    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.

8 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    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's avatar
      ElliotP
      Post Prodigy
      9 years ago

      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's avatar
        v-huizhn-msft
        Microsoft Employee
        9 years ago

        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