Deneb Sorting Issue when "Datum" is included

[vamshi_pbims]

Hi Team,

I'm using deneb to build my visual using below code, So my target is, I will have sum of price which i will be showing on one layer and sum of profit (Target) showing as as another bar in another layer, however my MonthYear sorting is working correctly untill I add the Datum bar(Target), but as soon as i add it the sort order getting disturbed. I want my targte bar to be at last.

PFA screen shots, with Target bar and without target bar.

I have tried all the ways including entering manula sorting in the code itself, but still no result.

{
  "data": {
    "name": "dataset"
  },
  "layer": [
    {
      "mark": {
        "type": "bar",
        "tooltip": true
      }
     ,

 "encoding": {
        "x": {
          "field": "MonthYear",
          "sort": {
            "field": "SortOrd",
            "order": "descending"
          },
          "type": "nominal"
        },
        "y": {
          "field": "Sum of Price",
          "type": "quantitative"
        }
      } 
    },
    {
      "mark": {
        "type": "bar",
        "tooltip": true
        
      },
      "encoding": {
        "x": {
          "datum": "Target",
          "type": "nominal"
        },
        "y": {
          "field": "Sum of Profit",
          "type": "quantitative"
        }
      }
    }
  ]
 
}

Any help is highly apprciated.

Thanks.

[dm-p]

Hi @vamshi_pbims,

Even though you have specified your x-encoding separately in your layers, Vega-Lite will union them and there is likely something with how the second layer's data stream is being derived. The space allocated for Target on the axis shows artifacts on the bar, suggesting that Vega is trying to stack multiple rows into the same axis position, and this may need to be aggregated also, so that you get a single bar for this 'category' rather than many.

It's hard to propose a solution without your underlying data. Could you provide a sample of this in a copy/pastable format? If you can provide this, I'll see if I can provide what I think would be the solution for this.

Thanks.

Daniel

It's hard to propose

[vamshi_pbims]

Hi @dm-p ,

Thank you for taking time to look into this.

PFB Data.

PRODUCT:

Product	Price	Profit	Date
A	10		10-11-2024
B	20		12-12-2204
C	30		03-03-2025
D	40		04-04-2025
E	50	5	06-06-2025

So in the above table, I consider one future date for last row because im connecting this date with my Calendar table to show the last profit value at the last in chart as target(using X-axis Field for this).

And i'm following two approaches here.

1) Without datun bar, directly included target value in Product table itself.

Target = IF(SELECTEDVALUE(PRODUCT)<>"E", SUM(PRICE),SUM(PROFIT))

X-Axis Field = IF(SELECTEDVALUE(PRODUCT)="E","TARGET", SELECTEDVALUE(MONTHYEAR))

In this way, I can get the desired output, but when i use date slicer and if i slice data less than the future date, the target bar will goal away even if i include ALL(Calendar) in my target measure in Profit section. But I want the target bar to be there no matter what date range is selected in slicer.

2) take a seperate table for Target data as below and take datum bar.

SEPEREATETARGET:

Product	Profit	Date
E	5	06-06-2025

In this case I'm using below measure,

Target = CALCULATE(SUM('SEPARATETARGET'[PROFIT]), ALL(CALENDAR))

If I use this measure in datum bar, the target bar is not going away even if i change dates in my slicer but sorting is not working. for this i use code like below.

{
      "mark": {
        "type": "bar","tooltip":true
        
      },
      "encoding": {
               
        "x": {
          "datum": "Target",
          "type": "nominal"
        },
        "y": {
          
          "field": "Target_Measure",
          "type": "quantitative"
        
        }
       
      }
    }

Yes, you are correct I was supoosed to put "aggregate":"sum" step.

PFA, Screenshot of my data model.

Please let me know, if you need any other inputs from my side. If you would like, I can provide with the pbix file too.

Thanks.

[dm-p]

Hi @vamshi_pbims

This is a great amount of detail but not quite exactly what I need to help you. 

Visuals can't see the model or the DAX; they only see the result of the query that Power BI executes on their behalf. What will help the best is if you can provide a sample of all columns and values for the generated dataset in Deneb (from the looks of your spec this will be at least MonthYear, SortOrd, Price and Profit). If this is in the same format as the sample data you provided in your reply I can create an artificial dataset that should match yours.

Thanks, 

Daniel

[vamshi_pbims]

Hi @dm-p ,

Please find the PBIX from below link.

Target_Pbix 

Please let me know, if you have any issues with acees.

Thanks.

[dm-p]

Hi @vamshi_pbims, and thanks for the details.

I'm not good enough at DAX to solve the first problem, but solving the second may help. You'll need to validate this, though.

The strategy here of using datum to substitute the value of 'Target' is an interesting approach, and will sort of work, but doesn't fulfill the requirement of having a sort order that can be unioned also. Additionally, because of how Vega-Lite unions scales with a sort clause, you will need an aggregate of min, max, count or a boolean value in there. This is provided as a warning in Vega Editor:

For some reason, this doesn't appear in Deneb; I'll have to have a look at why that is.

As such, the approach I've taken is to:

1. Aggregate the target value using a transform
2. Calculate a field called MonthYear for this layer after aggregation, with a value of 'Target'
3. Calculate a field called SortOrd for this layer after aggregation with a value of 999912 (which would be akin to a 'no date' row in a date dimension).

This layer's dataset will look as follows in the debugger:

These fields and values now match the "shape" of the primary layer and will resolve. The only other thing to do is to add an aggregate to the sort clause. Because the scale is grouped by the granularity of the data, either a max or a min will be OK.

Here's the resulting output:

I've made a couple of additional changes to the encoding channels, as they work hierarchicallyin Vega-Lite. Revised spec is as follows:

{
  "data": {
    "name": "dataset"
  },
  "transform": [
    {
      "filter": "datum['Sum Of Price'] != null "
    }
  ],
  "layer": [
    {
      "mark": {
        "type": "bar",
        "tooltip": true
      },
      "encoding": {
        "y": {
          "field": "Sum Of Price"
        }
      }
    },
    {
      "transform": [
        {
          "aggregate": [
            {
              "field": "Target_m",
              "op": "sum",
              "as": "Target_m"
            }
          ]
        },
        {
          "calculate": "'Target'",
          "as": "MonthYear"
        },
        {
          "calculate": "999912",
          "as": "SortOrd"
        }
      ],
      "mark": {
        "type": "bar",
        "tooltip": true
      },
      "encoding": {
        "y": {
          "field": "Target_m"
        }
      }
    }
  ],
  "encoding": {
    "x": {
      "field": "MonthYear",
      "sort": {
        "field": "SortOrd",
        "op": "min"
      },
      "type": "ordinal"
    },
    "y": {
      "type": "quantitative"
    }
  }
}

I've also attached an updated copy of your workbook, so hopefully you can have a look, test and see if this solves problem #1 as well.

Thanks!

Daniel

[vamshi_pbims]

Hi @dm-p ,

The way sorting works now is correct, however if you see the Target bar, in the PBIX or the screen shot you attached, it is way higher than the overall sum of profit in Target Table (15 ), and its changing when i change dates in my slicer and reaching around 135 too. It is supposed to be 15 and not more that.

Please let me know, If you need any other details.

Thanks,

Vamshi.

Thanks.

[dm-p · Accepted solution]

If you want to use one instance of the value and it's repeated for each row, can you use average or min as an aggregate instead of sum?

[vamshi_pbims]

Hi @dm-p  ,

Thank you very much for the help, 

I had to modify the code like below from your code, to get the output for my requirement. And I have commeneted the aggregated part inorder to make my legend working, else its showing as "Undefined" for "Target" bar. And I had to remove "SortOrd" field from visual otherwise the datum bar is coming at starting in the chart instead of end. The trick for my requirement was to use sort: {"op":"min"} in the encoding.

{
  "data": {
    "name": "dataset"
  }
    ,
   "transform": [
    {
      "filter": "datum['Sum Of Price'] != null "
    }
  ]
  ,

  "layer": [
    {
      "mark": {
        "type": "bar",
        "tooltip": true
      }}
     ,


   {
    
    
      "mark": {
        "type": "bar",
        "tooltip": true
        
      },
      "encoding": {
        "x":{"datum":"Target","type": "nominal"},
        "y": {
          "aggregate":"min",
          "field": "Target_m",
          "type": "quantitative"
        }
      }
    }
  ]
 ,
  "encoding": {
        "x": {
          "field": "MonthYear",
         "sort":{"field":"SortOrd","op": "min"
          },
          "type": "nominal"
        },
        "y": {
          "field": "Sum Of Price",
          "type": "quantitative"
        },"color": {"field":"Product"}
      } 
}

Thanks Again,

Vamshi.