cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

How can i show 2 dimension on X Axis - Clustered Column Chart WITH multiple YEAR filter selection

Hi All, I have tried explaing my Problem in details related to showing multiple dimensions on X -Axis WITH filter based on multiple Year 

 

I have 3 Tables with following columns

Table A --> Date(mm-dd-yyyy) and Serial Number

Table B  --> Serial Number , Invoice Number 

Table C --> Invoice Number , AA (Text field ), BB (Whole Number)

I have built a relationship as follows :

Table A  (*:1) Table B  matching Serial Number 

Table B  (1:*) Table C  matching Invoice Number 

 

Based on the Serial Number selected from Table B , I want to show data at Quarter (Q1,Q2,Q3,Q4)  Level on X - Axis.

For multiple year selected from Date as a filter  , I should get a column for Count of AA and Sum of BB for each YEAR.

So , If i select 2016 and 2026 , for Q1 i should get 4 Columns . 2 column for 2016 i.e. Count of AA and Sum of BB and same for 2026.

 

I have used Clustered Column Chart, with following fileds :

Axis : Quarter from Date Column from Table A,

Legend : Year from Date Column from Table A,

Value : I am NOT able to drag BOTH AA and BB , I can do either AA or BB at a time. 

BUT  if is remove Year from Legend , than i can drag both AA and BB in this case i am NOT able to filter my data by Year.

 

Is there any restriction between Legend and Value selection ? I also tried using combo chart but it does not work.

 Attached Report showing only with Count of AA , BUT sum of BB is still missing. Please guide . Thank you.

 

 Sample_Graph_Clustered_Column_Chart.png

1 ACCEPTED SOLUTION
ankitpatira
Community Champion
Community Champion

@Anonymous most of the visuals come in PowerBI only allows one field in Value section. You can't drag and drop multiple there as compared to axis where you get drill down.

 

Easy way to do what you trying to achieve would be to have two column chart visuals side by side one with value for Count of AA and other with value of Sum of BB. I have faced similar situations earlier where I've just used two visuals side by side.

View solution in original post

7 REPLIES 7
Prathap1
Frequent Visitor

Hi all,  

 

I'm a newbie. Started learning power BI while creating  "Line and clustered column chart" I was facing issue 

Below is my data screen shot 

 

 

Data.png/

 

I need SchoolCode- "Axis", Bar1 -Last week, Bar2-Current Week, Line - Budget

 

Chart.png

It is showing the count of the Last week, Current Week and Budget. I don't need the count I want the values. Please help me in this 

 

Thanks in Advance

Regards,

Prathap S

Anonymous
Not applicable

@Prathap1,

Just based on the screenshot it looks like you may have your measures changed to Counts rather than using a Sum measure. All of your bars add up to "1" for every dimension. 

 

Check out this to see how to change how the measure itself is calculating: https://powerbi.microsoft.com/en-us/documentation/powerbi-service-aggregates/

 

It may help.

Jonathan

Anonymous
Not applicable

@Anonymous, I realize this is an old post, but apparently you should be able to do this now with the November 2016 release: https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-november-feature-summary/#hierarchicalAxis

 

But for some reason I can't get it to work on my particular data set.

ankitpatira
Community Champion
Community Champion

@Anonymous most of the visuals come in PowerBI only allows one field in Value section. You can't drag and drop multiple there as compared to axis where you get drill down.

 

Easy way to do what you trying to achieve would be to have two column chart visuals side by side one with value for Count of AA and other with value of Sum of BB. I have faced similar situations earlier where I've just used two visuals side by side.

View solution in original post

Sean
Community Champion
Community Champion

Hello, does that means that there is no way we could do the two level axis label as for now (e.g. see the screenshot as follow)? Thanks.

 

Anonymous
Not applicable

@@KTP_UB   I don't think you can do this.

Secondly, the point i raised above was that you can't use more than 2 different dimension on the same X-Axis.Thanks

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.