Deneb - Dynamic Currency?

Deneb Showcase
1

ShowcaseDashboard.pbix (3.9 MB)
Hi Enterprise DNA,

is there a possible way to have a dynamic currency in Deneb?

my currency table is TargetCurrency table

image
image1032×468 13.7 KB

if i select gbp, it will have a currency symbol above for gbp, or any currency that i will choose in the slicer?
heres my deneb code

{
  "data": {"name": "dataset"},
  "title": {
    "text": "Sales Amount",
    "anchor":"start",
    "fontStyle": "sans-serif",
    "fontWeight": "lighter",
    "fontSize": 15
  },
  "encoding": {
    "x": {
      "type": "quantitative",
      "scale": {"nice": false},
      "title": null,
      "axis": null
    }
  },
  "layer": [
    {
      "mark": {
        "type": "bar",
        "color": "#ccc",
        "size": 40,
        "yOffset": 10,
        "tooltip": true
      },
      "encoding": {
        "x": {"field": "Sales Amount LY"},
        "y": {
          "title": null
        }
      }
    },
    {
      "mark": {
        "type": "bar",
        "color": "#605E5C",
        "size": 20,
        "yOffset": 10,
        "tooltip": true
      },
      "encoding": {
        "x": {
          "field": "Sales Amount",
          "legend": "Sales"
        },
        "color": {
          "condition": [
            {
              "test": "datum['KPI Sales'] === 1",
              "value": "red"
            }
          ],
          "value": "green"
        }
      }
    },
    {
      "mark": {
        "type": "tick",
        "color": "orange",
        "size": 50,
        "yOffset": 10,
        "thickness": 2,
        "tooltip": true
      },
      "encoding": {
        "x": {
          "field": "Sales Amount Target",
          "title": null
        }
      }
    },
    {
      "mark": {
        "type": "text",
        "color": "black",
        "dx": -5,
        "dy": 0,
        "xOffset": 0,
        "yOffset": 50,
        "angle": 0,
        "align": "center",
        "baseline": "bottom",
        "fontSize": 10,
        "fontWeight": "normal",
        "limit": 0
      },
      "encoding": {
        "text": {
          "field": "Sales Amount Target",
          "format": ".2s"
        },
        "x": {
          "field": "Sales Amount Target",
          "type": "quantitative"
        }
      }
    },
    {
      "mark": {
        "type": "text",
        "color":"black",
        "dx": 0,
        "dy": 0,
        "xOffset": 0,
        "yOffset": -40,
        "angle": 0,
        "align": "right",
        "baseline": "bottom",
        "font": "sans-serif",
        "fontSize": 14
        ,
        "fontStyle": "normal",
        "fontWeight": "normal",
        "limit": 0
      },
      "encoding": {
        "x": {
          "aggregate": "max",
          "field": "Sales Amount",
          "type": "quantitative"
        },
        "text": {
          "field": "Sales Amount",
          "format": ".2s"
        }
      }
    },
    {
      "mark": {
        "type": "text",
         "color":"black",
        "dx": 0,
        "dy": 0,
        "xOffset": 0,
        "yOffset": -40,
        "angle": 0,
        "align": "right",
        "baseline": "top",
        "font": "sans-serif",
        "fontSize": 10,
        "fontStyle": "normal",
        "fontWeight": "normal",
        "limit": 0
      },
      "encoding": {
        "x": {
          "aggregate": "max",
          "field": "Sales Amount",
          "type": "quantitative"
        },
        "text": {
          "field": "Sales Amount (Label)"
        }
      }
    }
  ]
}
2

Hi @sgrady. Please create a PBIX using sample data that illustrates your issue and include a marked-up screenshot of your mockup of your desired outcome.
Greg

3

Hi @sgrady.

There’s no way I know of to add a currency symbol dynamically in Deneb/Vega-Lite, but it can easily be done in DAX. Here’s a solution where I’ve created such a [Currency Symbol] field using a calculated column

Currency Symbol = 
SUBSTITUTE(
    SUBSTITUTE(
        LEFT( TargetCurrency[FormatString], 4 ),
        "#", ""
    ),
    ",", ""
)

eDNA Forum - Deneb Dynamic Currency - 1
eDNA Forum - Deneb Dynamic Currency - 12998×980 245 KB

and then included it in the dataset passed to the Deneb visual. Then, a simple adjustment to the text calculation is needed along with the addition of a transform block:

{
  "data": {"name": "dataset"},
  "title": {
    "text": "Sales Amount",
    "anchor": "start",
    "fontStyle": "sans-serif",
    "fontWeight": "lighter",
    "fontSize": 15
  },
  "transform": [
    {
      "calculate": "datum['Currency Symbol'] + format( datum['Sales Amount'], '.2s' )",
      "as": "_sales_amount_with_symbol"
    },
    {
      "calculate": "datum['Currency Symbol'] + format( datum['Sales Amount Target'], '.2s' )",
      "as": "_sales_amount_target_with_symbol"
    }
  ],
  "encoding": {
    "x": {
      "type": "quantitative",
      "scale": {"nice": false},
      "title": null,
      "axis": null
    }
  },
  "layer": [
    {
      "mark": {
        "type": "bar",
        "color": "#ccc",
        "size": 40,
        "yOffset": 10,
        "tooltip": true
      },
      "encoding": {
        "x": {
          "field": "Sales Amount LY"
        },
        "y": {"title": null}
      }
    },
    {
      "mark": {
        "type": "bar",
        "color": "#605E5C",
        "size": 20,
        "yOffset": 10,
        "tooltip": true
      },
      "encoding": {
        "x": {
          "field": "Sales Amount",
          "legend": "Sales"
        },
        "color": {
          "condition": [
            {
              "test": "datum['KPI Sales'] === 1",
              "value": "red"
            }
          ],
          "value": "green"
        }
      }
    },
    {
      "mark": {
        "type": "tick",
        "color": "orange",
        "size": 50,
        "yOffset": 10,
        "thickness": 2,
        "tooltip": true
      },
      "encoding": {
        "x": {
          "field": "Sales Amount Target",
          "title": null
        }
      }
    },
    {
      "mark": {
        "type": "text",
        "color": "black",
        "dx": -5,
        "dy": 0,
        "xOffset": 0,
        "yOffset": 50,
        "angle": 0,
        "align": "center",
        "baseline": "bottom",
        "fontSize": 10,
        "fontWeight": "normal",
        "limit": 0
      },
      "encoding": {
        "text": {
          "field": "_sales_amount_target_with_symbol"
        },
        "x": {
          "field": "Sales Amount Target",
          "type": "quantitative"
        }
      }
    },
    {
      "mark": {
        "type": "text",
        "color": "black",
        "dx": 0,
        "dy": 0,
        "xOffset": 0,
        "yOffset": -40,
        "angle": 0,
        "align": "right",
        "baseline": "bottom",
        "font": "sans-serif",
        "fontSize": 14,
        "fontStyle": "normal",
        "fontWeight": "normal",
        "limit": 0
      },
      "encoding": {
        "x": {
          "aggregate": "max",
          "field": "Sales Amount",
          "type": "quantitative"
        },
        "text": {
          "field": "_sales_amount_with_symbol"
        }
      }
    },
    {
      "mark": {
        "type": "text",
        "color": "black",
        "dx": 0,
        "dy": 0,
        "xOffset": 0,
        "yOffset": -40,
        "angle": 0,
        "align": "right",
        "baseline": "top",
        "font": "sans-serif",
        "fontSize": 10,
        "fontStyle": "normal",
        "fontWeight": "normal",
        "limit": 0
      },
      "encoding": {
        "x": {
          "aggregate": "max",
          "field": "Sales Amount",
          "type": "quantitative"
        },
        "text": {
          "field": "Sales Amount (Label)"
        }
      }
    }
  ]
}

Hope it helps.
Greg
eDNA Forum - Deneb Dynamic Currency.pbix (3.9 MB)

1 Like
4

Thank you so much Greg, it worked!