CODEWITHSUNDEEP
powerbiconditional-formatting
LordRofticus
LordRofticus

Reputation: 237

Power BI Conditional Formatting on Visual using Field Parameters

I have a Power BI report that uses a Field Parameter with a list of KPIs that the users can pick from to dynamically change the visuals on a page.

enter image description here

The stakeholders of the report is requesting something I find unconventional. They want a visual to indicate where the average of the is by showing above and below average of the selected KPI Field Parameter in different colors. Example below was edited in MS Paint to show what needs to happen:

enter image description here

Now based on my knowledge you can set some sort of conditional formatting of the bar colors based on rules. However it seems this option is not available for a visual that uses a KPI Field Parameter.

enter image description here

The stakeholders are adement this can be done and have show me some weird example.

Question: How can one get this to work?

Sample PBIX File: Drop Box File

Upvotes: 0

Views: 548

Answers (1)

Sam Nseir
Sam Nseir

Reputation: 12101

Unfortunately there isn't a mechanism to get the selected Field Parameter calculated value. So you will need to use a SWITCH statement to determine which one is selected and then perform the calculation.

Based on your sample PBIX (thanks), you can create a measure like:

CF KPI1 = 
  var dimValue = SWITCH(SELECTEDVALUE('KPI1'[KPI1 Fields]),
    NAMEOF('Data'[Avg Qty]), 'Data'[Avg Qty],
    NAMEOF('Data'[Avg Order Cost]), 'Data'[Avg Order Cost],
    NAMEOF('Data'[Avg Profit Margin]), 'Data'[Avg Profit Margin],
    NAMEOF('Data'[Avg Rating]), 'Data'[Avg Rating]
  )
  var allValue = SWITCH(SELECTEDVALUE('KPI1'[KPI1 Fields]),
    NAMEOF('Data'[Avg Qty]), CALCULATE('Data'[Avg Qty], ALLSELECTED('Data')),
    NAMEOF('Data'[Avg Order Cost]), CALCULATE('Data'[Avg Order Cost], ALLSELECTED('Data')),
    NAMEOF('Data'[Avg Profit Margin]), CALCULATE('Data'[Avg Profit Margin], ALLSELECTED('Data')),
    NAMEOF('Data'[Avg Rating]), CALCULATE('Data'[Avg Rating], ALLSELECTED('Data'))
  )
  return SWITCH(TRUE(),
    ISBLANK(allValue), BLANK(),
    dimValue < allValue, "Below",
    dimValue >= allValue, "Above"
  )

And then for the Bars Color conditional formatting:

enter image description here

Just thought I'd share this other version which would be more manageable/re-useable.

Create a Measure to return the selected calculation:

KPI1 First calculation = 
  SWITCH( MIN('KPI1'[KPI1 Fields]),
    NAMEOF('Data'[Avg Qty]), 'Data'[Avg Qty],
    NAMEOF('Data'[Avg Order Cost]), 'Data'[Avg Order Cost],
    NAMEOF('Data'[Avg Profit Margin]), 'Data'[Avg Profit Margin],
    NAMEOF('Data'[Avg Rating]), 'Data'[Avg Rating],
    NAMEOF('Data'[Total Qty]), 'Data'[Total Qty],
    NAMEOF('Data'[Total Order Cost]), 'Data'[Total Order Cost]
  )

Then for your CF measure you can have:

CF KPI1 = 
  var dimValue = [KPI1 First calculation]
  var allValue = CALCULATE([KPI1 First calculation], ALLSELECTED('Data'))
  return SWITCH(TRUE(),
    NOT CONTAINSSTRING(SELECTEDVALUE('KPI1'[KPI1 Fields]), "Avg"), BLANK(),
    dimValue < allValue, "Below",
    dimValue >= allValue, "Above"
  )

Upvotes: 1

Related Questions