cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
orana
Regular Visitor

Graphing data on chart

Hello,

 

I'm looking to create a chart, where the x-axis has account names and y-axis has reveune. I want to create a filter such that the graph only show data pretaining accounts that have both company1 and company2 information. 

examples.JPG

 

1 ACCEPTED SOLUTION

@orana 

pls try this

Column = if(CALCULATE(DISTINCTCOUNT('Table (2)'[company]),ALLEXCEPT('Table (2)','Table (2)'[account name]))>1,"True",BLANK())

1.PNG





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

Proud to be a Super User!




View solution in original post

7 REPLIES 7
orana
Regular Visitor

Hey!

Thanks for your help. It looks like both formulas (table and column) isn't filtering the data as needed. Its still showing data which only has company 1 info. Whereas i would like to only present account and revenue info for accounts are purchasing from both company 1 and company 2. 

Is there a way to create a column where it looks at the account name column and then sees if this account has both company 1 and company 2 listed under the company column. If yes, the column will return a "true", if not it will be blank?

 

@orana 

pls try this

Column = if(CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[account name]))>1,"True",BLANK())

1.PNG





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

Proud to be a Super User!




I think i see your logic. I think the image below is a better presentation of my data. Based on the code above, i added a true column. You can see that the yellow highlighted column would also yield a true even though it only has c1 listed under company. Is there a way to develop a formula such that only account 1 would appear Capture555.JPG

@orana 

pls try this

Column = if(CALCULATE(DISTINCTCOUNT('Table (2)'[company]),ALLEXCEPT('Table (2)','Table (2)'[account name]))>1,"True",BLANK())

1.PNG





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

Proud to be a Super User!




View solution in original post

orana
Regular Visitor

Is there a way to do this such that i can use it as a filter on a page vs. creating a new table with just the accounts and associated revenue listed? I would like to toggle this feature on and off as i walk through the data

@orana 

maybe can create a new column and use that column to filter data

Column = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[account name]))

pls see the attachment below





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

Proud to be a Super User!




ryan_mayu
Super User II
Super User II

@orana 

maybe you can create a new table

TABLE2 = 
VAR tbl=ADDCOLUMNS('Table',"number",CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[account name])))
RETURN SELECTCOLUMNS(FILTER(tbl,[number]>1),"account name",'Table'[account name],"revenue",'Table'[REVENUE])




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

Proud to be a Super User!




Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors