Hello all; This could be a dummies question but here is the scenarios: I have created a clusterred Column Chart to compare monthly values for 2015 and 2016. I have a line value to measure a slighly different measure as shown on the picture below. what happens is that the line values for the first three months are culmulative of 2015 + 2016. What I really want is for the line to represent 2015 only, not the total 2015 + 2016. I have poked around the forum and did my own research but could not find an answer yet. Please let me know if you have a suggestion for me. Thanks in advanced for your help.Have a great dayAndrew

You can have Several Measures in the Line Values of a Line and Clustered Column Chart. If you have several columns and only one line - the value in that line will be aggregated for all columns So you'll have to create measures that in your case count only those 2 specific years...

So the issue is your chart is relying on the column series to provide the yearly filter. But there is no such filtering on your line measure. If you specifically want (ie always want) 2015, just write a measure like this and replace it for your line =calculate([current line measure],YourCalendarTable[Year] = 2015) If you somehow want the line to be the "previous year", then you would need something like this. =calculate([current line measure],YourCalendarTable[Year] = max(YourCalendarTable[Year])-1)

Thanks MattAllington for your help on this. I really appreciate it. I have created a date dimension table to use in the measure. I however run into another error message: "A single value for column 'Set Date' in table 'Meters Sold vs Set' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result." I am counting a number of occurance in the Set Date column as a measure here. This field is a date field (mm/dd/yyyy). I am wondering if we need a countable field to be referenced here?

AndrewDang Set Date 2015 = CALCULATE(COUNTA('Meters Sold vs Set'[Set Date]), 'Date Dimension'[Year] = 2015) or try should give you same result Set Date 2015 = CALCULATE(COUNTROWS('Meters Sold vs Set'), 'Date Dimension'[Year] = 2015)

AndrewDang wrote: I have created a date dimension table to use in the measure. I however run into another error message: "A single value for column 'Set Date' in table 'Meters Sold vs Set' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result." sorry, my bad mistake. The second formula should be =calculate([current line measure],filter(YourCalendarTable,YourCalendarTable[Year] = max(YourCalendarTable[Year])-1)) Replace [current line measure] with what ever you are trying to do (counting something in this case).update the table and column names too of course

Help with Clustered Column Chart | Microsoft Fabric Community

15 Replies

Sean
Community Champion
10 years ago
You can have Several Measures in the Line Values of a Line and Clustered Column Chart.

If you have several columns and only one line - the value in that line will be aggregated for all columns

So you'll have to create measures that in your case count only those 2 specific years...
MattAllington
Community Champion
10 years ago
So the issue is your chart is relying on the column series to provide the yearly filter. But there is no such filtering on your line measure. If you specifically want (ie always want) 2015, just write a measure like this and replace it for your line

=calculate([current line measure],YourCalendarTable[Year] = 2015)

If you somehow want the line to be the "previous year", then you would need something like this.

=calculate([current line measure],YourCalendarTable[Year] = max(YourCalendarTable[Year])-1)
- AndrewDang
  Helper IV
  10 years ago
  Thanks MattAllington for your help on this. I really appreciate it.
  
  I have created a date dimension table to use in the measure. I however run into another error message: "A single value for column 'Set Date' in table 'Meters Sold vs Set' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
  
  I am counting a number of occurance in the Set Date column as a measure here. This field is a date field (mm/dd/yyyy). I am wondering if we need a countable field to be referenced here?
  - Sean
    Community Champion
    10 years ago
    AndrewDang
    
    Set Date 2015 = CALCULATE(COUNTA('Meters Sold vs Set'[Set Date]), 'Date Dimension'[Year] = 2015)
    
    or try should give you same result
    
    Set Date 2015 = CALCULATE(COUNTROWS('Meters Sold vs Set'), 'Date Dimension'[Year] = 2015)