powerbidax

Displaying a Fixed Timeline in a Power BI Line and Clustered Column Chart with Slicers

I have created a Line and Clustered Column Chart in Power BI where the X-axis represents months, the columns show costs, and the lines represent cost percentages. The legends indicate different snapshots of the data at specific timelines.

I want to always display a specific timeline (e.g., a particular snapshot) in the chart while allowing users to slice or filter the other timelines.

How can I achieve this functionality in Power BI?

Currently, I create a measure that filters the data for both the line and column

FY24 Mfg Rev = 
CALCULATE(
    [MC_R_Percentage], 
    fy24_monthly[Timeline] = "FY24")

to always show the desired timeline but this creates a disconnected chartenter image description here
enter image description here
[this is what the chart would look like without creating another measure]

I'm hoping to be able to achieve without the need to create a new measure not just for better visuals also because the i have created a parameter to change to measures using slicers, if i create another measure just to display a specific timeline the parameter does not change it which creates a whole other problem

Solution

  • Try creating this one measure:

    Chart Mfg Rev = 
  // as you have it above, this will always show for FY24, so long the slicer is on fy24_monthly[Timeline]
  var fy24 = 
    CALCULATE(
      [MC_R_Percentage],
      fy24_monthly[Timeline] = "FY24"
    )

  // for the slicer, assuming the slicer is on fy24_monthly[Timeline]
  var nonfy24 = 
    CALCULATE(
      [MC_R_Percentage],
      KEEPFILTERS(fy24_monthly[Timeline] <> "FY24")
    )

  return COALESCE(nonfy24, fy24)

    Supplemental to comments
    To use Field Parameter, you will need to do so via SWITCH. For example, create a new Measure similar to:

    Percentage_Parameter Value =
  SWITCH(
    SELECTEDVALUE('Percentage_Parameter'[Parameter Fields]),
    NAMEOF([MC_R_Percentage]), [MC_R_Percentage],
    NAMEOF([MC_PR_Percentage]), [MC_PR_Percentage]
  )

    Then use this new measure [Percentage_Parameter Value] in your expressions (like the answer above).