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 (tex...
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.

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

ElliotP's avatar
ElliotP
Post Prodigy
9 years ago

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

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

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

        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