Forum Discussion
Dynamic Horizontal reference line linked to Slicer
Hi,
My first post here. I'm also new to Power BI / DAX and haven't been able to find a solution to the below:
- I've got a line and clustered column chart (hence no analytics pane options for constant line)
- Columns show revenue per day
- I need a horizontal constant line to show daily revenue targets that is dynamically linked to slicer/filter for revenue by Contract type/code
- I've used this DAX formula:
IF(
ISFILTERED('Revenue & Orders'[ContractCode]),
SELECTEDVALUE ('Revenue & Orders'[Rev.Target]),
SUM(RevenueTargets[RevenueTarget])
)
- The above is working fine with either "Select all" or select one contract code on the slicer but when you select more than one contract type/code then it doesn't work, i.e. doesn't sum up rev.target for just those two, three, or however many contract codes.
- For example,
- if I select Contract A on the slicer, a constant line at 3,000 apears on the chart
- if I Select all on the slicer, a constant line at 9,000 apears on the chart, but
- But if I select, say, Contract A and C on the slicer, I would want to see a constant line at 5,500 to apear on the chart (sum of A and C), but that's not happening
Please help!
| Contract code | Rev.Target |
| A | 3,000 |
| B | 2,000 |
| C | 2,500 |
| D | 1,000 |
| E | 500 |
| Total | 9,000 |
