Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Price Change Filter

I have been tasked to see if there is a way (formula) in Power BI that would allow users to see only items that had a predetermined price change over the prior period over a set price...they say a 3%...
Need Help
dkay84_PowerBI's avatar
dkay84_PowerBI
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

I would think the best approach here would be to have a table with incremental values, say "0.5%", "1%", "1.5%" etc. up to some limit, and use this as a slicer. Simultaneously, add a calc column to the original data that determines the price change (or if it comes from the data source) and then add another calc column for the percentage bucket.  Those buckets would mirror the buckets you made in the first table, and then you can build a relationship and use the slicer in the report.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

So now that I am mulling this over, if the filter had values of "0.5%", "1%", "1.5%" etc....what would happen if any of the changes are not exactly "0.5%", "1%", "1.5%" etc.  For instance my fear is if a results was a 1.15% change, would the filter exclude those items since there is not matching filter value?  Thoughts?

  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago
    That's why your calc column logic creates buckets:

    If([PriceChange]<=.005,"<=.05%",If(and([PriceChange]>.005,[PriceChange]<=.01),".05-1%,...)

    However, with this said, I wonder if you could use a numeric range slicer using your actual percentage change column.
  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Anonymous,

     

    Suppose there has been existing a incremental values column ("0.5%", "1%", "1.5%" etc) in your source table, you can create a new calculated table which contains only one column that referring this incremental values column. Please see below steps.

     

    Create calculated table.

    Table =
SELECTCOLUMNS (
    'Price Change Filter',
    "Change Per", 'Price Change Filter'[incremental values]
)

    Create a measure which returns desired item filtered by slicer.

    Measure1 =
CALCULATE (
    MAX ( 'Price Change Filter'[Change] ),
    FILTER (
        'Price Change Filter',
        'Price Change Filter'[Change] >= MAX ( 'Table'[Change Per] )
    )
)

    In clicer, you should add column 'Table'[Change Per]. In your visual, you should use above measure 'Measure1' rather than the column in source table.

     

    You can refer to this thread for detailed steps, it is not exactly the same as your scenario, but the logic is similar to yours.

     

    Best regards,
    Yuliana Gu