cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rax99 Member
Member

Hot to show (calc) column values as categories on line/bar chart

I have a basic table that looks like this;

 

CIDSDSD2SD3SD_flagSD2_flagSD3_flag
674432177354531  1
674433177354531  1
6744341773545311  
6744351773545311  
6744361773545311  
674437177354531 1 
674438177354531 1 
6744391773545311  
674440177354531 1 
6744411773545311  
674442177354531  1

 

I need to be able to 'group' or 'bucket/bin' the SD, SD2,  SD3 values on the axis and sum up the flags for each one. So the resulting chart should look like this;

SDbuckets.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Any ideas how this can be achieved using DAX (or any other way) from the table above?

1 ACCEPTED SOLUTION

Accepted Solutions
ssugar Member
Member

Re: Hot to show (calc) column values as categories on line/bar chart

Ok, here's a better solution.  No need for the calculated table from before.  Create a new calculated column (Modeling > New Column) with the following DAX:

 

Bucket = IF(Table1[SD_flag] = 1, "SD1", IF(Table1[SD2_flag] = 1, "SD2", "SD3"))

 

Then the above "Bucket" calculated column will be the Axis, and "Count of Bucket" will be the value.

 

With this solution, you'll be able to add a slicer for CID and the chart will update properly.

View solution in original post

7 REPLIES 7
Super User
Super User

Re: Hot to show (calc) column values as categories on line/bar chart

Is this the raw data or what you have in DAX? I ask because if it is raw data you may want to pivot it.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

ssugar Member
Member

Re: Hot to show (calc) column values as categories on line/bar chart

Assuming your table is your raw data, you could do something like this with a calculated table (measure > new table), but it's probabaly not the cleanest way to do it, and would require further work if you add in more SD# buckets.

 

Table = UNION(ROW("Bucket", "SD1" , "Value", COUNTROWS(FILTER(Table1, Table1[SD_flag] = 1))), ROW("Bucket", "SD2" , "Value", COUNTROWS(FILTER(Table1, Table1[SD2_flag] = 1))), ROW("Bucket", "SD3" , "Value", COUNTROWS(FILTER(Table1, Table1[SD3_flag] = 1))))

Then you'd add "Bucket" as the Axis, and Value as the value on a line or bar chart.

Community Support Team
Community Support Team

Re: Hot to show (calc) column values as categories on line/bar chart

Hi @rax99,

 

By my tests, the solution of ssugar should be useful. 

 

If you have solved your problem, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

If you still need help, please feel free to ask.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
rax99 Member
Member

Re: Hot to show (calc) column values as categories on line/bar chart

Thanks this does work, however when I filter using values from another table this doesnt filter through on this chart. How can I build this same logic but also include the CID as this will be the logical join onto other datasets.

 

And yes this is the RAW table.

ssugar Member
Member

Re: Hot to show (calc) column values as categories on line/bar chart

Ok, here's a better solution.  No need for the calculated table from before.  Create a new calculated column (Modeling > New Column) with the following DAX:

 

Bucket = IF(Table1[SD_flag] = 1, "SD1", IF(Table1[SD2_flag] = 1, "SD2", "SD3"))

 

Then the above "Bucket" calculated column will be the Axis, and "Count of Bucket" will be the value.

 

With this solution, you'll be able to add a slicer for CID and the chart will update properly.

View solution in original post

Community Support Team
Community Support Team

Re: Hot to show (calc) column values as categories on line/bar chart

Hi @rax99,

 

Have you solved your problem?

 

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

 

If you still need help, please feel free to ask.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
rax99 Member
Member

Re: Hot to show (calc) column values as categories on line/bar chart

Simple and effective. Thanks very much

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 217 members 2,333 guests
Please welcome our newest community members: