DENEB - Problem with adding line to barchart

[RvdH1212]

Dear all, @giammariam, 

As mentioned in earlier messages I've just became aware of DENEB. It works all well, however when i try to add a line to my barchart, where the line is very sawy, instead of the expected smooth line. can someone see where this happens?

Thanks a lot!

Result:

Expection:

Code:

{
"data": {"name": "dataset"},
"transform": [
{
"calculate": "datum['Total balance'] - datum['Threshold']",
"as": "value_difference_from_threshold"
},
{
"calculate": "datum.value_difference_from_threshold >= 0 ? datum['Total balance'] : 0",
"as": "value_above_threshold"
},
{
"calculate": "datum.value_difference_from_threshold < 0 ? datum['Total balance'] : datum['Threshold']",
"as": "value_below_threshold"
}
],
"layer": [
{
"description": "Bank",
"mark": {
"type": "bar",
"tooltip": true
},
"encoding": {
"x": {
"field": "Datum",
"timeUnit": "yearmonthdate",
"title": "Date",
"type": "nominal",
"axis": {
"labelAlign": "center",
"labelExpr": "[timeFormat(datum.value,'%d'),timeFormat(datum.value, '%d')=='01'?timeFormat(datum.value, '%b'):'']"
}
},
"y": {
"field": "value_below_threshold",
"type": "quantitative",
"title": "EUR",
"axis": {
"format": ".1s"
}
},
"color": {"value": "#F75555"},
"tooltip": [
{
"field": "Total balance",
"format": "#,##0.",
"formatType": "pbiFormat"
}
]
}
},
{
"description": "VALUE ABOVE THRESHOLD",
"mark": {
"type": "bar",
"tooltip": true
},
"encoding": {
"x": {
"field": "Datum",
"type": "ordinal",
"axis": {
"title": "Date",
"labelAngle": 0
}
},
"y": {
"field": "Threshold",
"type": "quantitative",
"title": ""
},
"y2": {"field": "Total balance"},
"color": {
"condition": {
"test": "datum.value_difference_from_threshold < 0",
"value": "transparent"
},
"value": "#3B4D41"
},
"tooltip": [
{
"field": "Total balance",
"format": "#,##0.",
"formatType": "pbiFormat"
}
]
}
},
{
"description": "THRESHOLD",
"mark": {
"type": "rule",
"strokeWidth": 0,
"color": "grey"
},
"encoding": {
"y": {
"field": "Threshold",
"type": "quantitative",
"aggregate": "mean"
}
}
},
{
"description": "Smooth Line",
"mark": {
"type": "line",
"color": "#FF7200",
"tooltip": true
},
"encoding": {
"x": {"field": "Datum", "type": "ordinal"},
"y": {"field": "7-days average", "type": "quantitative", "title": "Total Balance"}
}
}
]
}

Dataset:

Date	Category	Total balance	7-days average	Threshold
1-1-2024	Volkswagen	369.563,21	2.609.332	0
1-1-2024	Green	335.582,64	32.822.741	0
1-1-2024	Sweden	270.893,36	895.695	0
1-1-2024	Brabant	128.544,42	709.905	0
1-1-2024	Left	108.131,11	785.727	0
1-1-2024	Glasses	-8.025.707,47	-1.557.933	0
2-1-2024	Green	9.762.913,29	31.457.644	0
2-1-2024	Sweden	3.280.134,87	1.021.928	0
2-1-2024	Volkswagen	2.529.806,59	2.559.458	0
2-1-2024	Brabant	1.858.963,39	638.368	0
2-1-2024	Left	514.542,01	757.986	0
2-1-2024	Glasses	-2.845.206,93	-1.983.518	0
3-1-2024	Green	48.013.953,31	32.339.179	0
3-1-2024	Volkswagen	4.993.542,98	2.654.511	0
3-1-2024	Sweden	1.185.068,44	1.024.921	0
3-1-2024	Brabant	976.708,42	512.763	0
3-1-2024	Left	409.171,67	724.047	0
3-1-2024	Glasses	-23.001.288,31	-3.594.754	0
4-1-2024	Brabant	8.365.657,51	689.887	0
4-1-2024	Volkswagen	6.367.517,34	2.531.866	0
4-1-2024	Sweden	1.188.697,58	1.027.210	0
4-1-2024	Left	798.846,86	661.094	0
4-1-2024	Green	-6.005.119,16	29.123.299	0
4-1-2024	Glasses	-20.258.566,16	-5.346.670	0
5-1-2024	Green	33.655.357,96	27.680.459	0
5-1-2024	Brabant	3.310.081,38	452.296	0
5-1-2024	Volkswagen	2.148.901,99	1.914.167	0
5-1-2024	Left	736.224,23	531.887	0
5-1-2024	Sweden	606.978,87	789.398	0
5-1-2024	Glasses	-36.690.735,70	-8.341.190	0
6-1-2024	Green	33.693.879,07	23.711.503	0
6-1-2024	Brabant	3.310.081,38	502.612	0
6-1-2024	Volkswagen	2.148.901,99	1.989.204	0
6-1-2024	Left	736.224,23	533.760	0
6-1-2024	Sweden	606.978,87	765.731	0
6-1-2024	Glasses	-36.690.735,70	-10.552.617	0

[lbendlin]

Your sample data is a bit inconclusive - is this in general what you are looking for?

[RvdH1212]

Hi Ibendlin,

Thanks for your response! Due to a lack of characters I had to reduce the example dataset. is there a posibility to provide more data? I cant do it in a regular message.

This is not exactly what i am looking for, the categories which are minus should be visible as minus and the categories in plus should be visible as plus. But the line is exactly how I want it.

So for 1 january:

1.212.714 and

-8.025.707

Where the negative amount (-8.025.707) is red and the plus amount (1.212.714) is green. This i wanted to achieve with the treshold function, which solved the problem. However, once adding the 7-days average line in the DENEB graph the line gets very sawy. Instead of the normal PowerBI Bar/Line chart, where it is smooth.

Thanks,

Richard

[lbendlin]

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...