Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
AndrewDang
Helper IV
Helper IV

Help with Clustered Column Chart

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.

Clustered bar and line chart.gif

 

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 day

Andrew

15 REPLIES 15
MattAllington
Community Champion
Community Champion

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)



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

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?

 

  DAX Error.gif


@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

 

 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.
Sean
Community Champion
Community Champion

@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)

 

Thanks @Sean for your help on this.

 

I had changed the formular.  However, I seam to have two identical lines. In other words, the fomular does not seem to alter my result.  Would you please take anther look at the screen below.

 

here is my fomular: Set Date 2015 = CALCULATE(COUNTA('Meters Sold vs Set'[Set Date]), 'DateKey'[Year] = 2015)

 

DAX Error -2016 8-42-12 AM.gif

 

Thanks Sean

Sean
Community Champion
Community Champion

@AndrewDang You have 2 options (each has its advantages)

 

OPTION 1

Create another Measure using the same formula just change 2015 to 2016

 

Set Date 2016 = ...... just change 2016 at the end

 

OPTON 2

You can also create (CY- Current Year and PY - Previous Year) - WATCH the results if you have Year Slicer though !!!

 

Set Date PY = CALCULATE(COUNTA('Meters Sold vs Set'[Set Date]), FILTER('DateKey', 'DateKey'[Year] = MAX('DateKey'[Year])-1))

 

Set Date CY = CALCULATE(COUNTA('Meters Sold vs Set'[Set Date]), FILTER('DateKey', 'DateKey'[Year] = MAX('DateKey'[Year])))

 

Let me know if this works!

 

 

@Sean

 

I have tried both options but my line does not seem to change at all.  i am not sure if my Date Dimension table is off? I have followed this tutorial to create my date table:

 

http://community.powerbi.com/t5/Desktop/How-do-i-create-a-date-table/m-p/23896/highlight/true#M7605

 

No manater what year I changed to, it does not seem to change the result at all.

Sean
Community Champion
Community Champion

Do you get the correct result form the Measures I gave you if you put in just a Table?

 

And post a screenshot so I can see what you have in the Vizualizations and Fields area?

Here is the screenshot with Visualizations and Fields boxes.  I had my Date Dimension goes out to 2017 so my current year = max -1; and previous year = max -2.  However, the two fomulars return identical results.  I have checked my data set and verified that I have set dates in both 2015 and 2016.

 

Thanks @Sean. I really appreciate your help here.

 

3-31-2016 9-35-13 AM.gif

Sean
Community Champion
Community Champion

@AndrewDang

 

In the Shared Axis try using Month from DateKey? If that doesn't work try removing all other stuff except Month from DateKey and the 2 Measures as in my picture?

 

CY&PY1.png

 

Thanks @Sean

 

I am able to get the two different lines for 2015 and 2016 now.  However, the set date and sold date numbers came out the same.  I am expect a little differenciate between the two numbers... I will spend more time troubleshooting this tomorrow but i am just wondering if you have any quick tips.

 

3-31-2016 3-24-04 PM.gif

 

Thanks Sean for your help.  It has been fun and great working on this.

 

Andrew

Sean
Community Champion
Community Champion

Can you describe in more detail what you want the final result to look like?

 

Also read this http://community.powerbi.com/t5/Desktop/quot-Shared-Axis-quot-for-two-date-columns-on-Clustered-Line...

 

And then follow the link in Matt's answer - because you may need the set up desribed there...

 

If you do you'll need to create separate Measures - one for Set Date and one for Sold Date

 

Thanks Sean;

 

I will keep digging this.  ultimately, I am trying to to do the same as what you had described on your blog. I need a line for # of Sold in a month (Sale in your example) and another line for # of Set for a month (Ship in your example).

 

I will keep digging this today to see if i can do this in Power BI.

 

Thanks for you help.

 

Happy Friday.

 

Andrew

 

Sean
Community Champion
Community Champion

@AndrewDang

 

Just add another inactive relationship as desribed in Matt's blog

http://exceleratorbi.com.au/multiple-relationships-between-tables-in-dax/

 

It should work!

 

Let me know if you cant make it work...

Sean
Community Champion
Community Champion

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...

 

Sample Chart.png

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.