cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
orana
Helper I
Helper I

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
Helper I
Helper I

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
Helper I
Helper I

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
Super User

@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
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

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