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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
edh
New Member

Beginner filtering question

Hi

I have a long excell with credit card expenses that I downloaded from my bank

 

for example

 

Company   Date             expense 

XXXX          01.01.18         $200

WWW         02.01.18           $40

ZZZZ           02.01.18        $100

 

A Bar Chart on this excell works amazing. You can see immediatly how much money you spend on XXX, the report summarizes it for you in one click.

Now, I created a nother exell names " Food Expenses" where I wrote down in one column a list of companies that are all related to grocery and food expenses.

And I joined the two excells by company name in one to many relationship

 

All I want to do now is another simple bar chart that shows me only the Food and Grocery companies that are in excell "Food and Expenses".

Please help

 

Thank you

 

EDH

 

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

Just create the bar chart putting the “business name” from the table “List of food and grocery companies” in the x-axis and the “expense” (ie the total debit amount) from the table “List of Debits” in the values field. 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

7 REPLIES 7
TomMartens
Super User
Super User

Hey,
basically this should be easy.

Without knowing the exact structure of your table I would recommend the following:

1. Create Slicer with the category from your expenses table
2. Create a bar chart and use the name of the company from the company table on axis and the expenses colum as value
3. select the food category in your slicer

Done!

Maybe you will get a company called (blank) on the axis, this indicates that there companie in your expenses table,that are not in your company table.

Hope this gives you an idea

Regards
Tom


Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi Tom

I am a beginner 

I see the slicer, but can you explain what do you mean when you say "with the category from your expenses table"
Which category should that be ?

Thank you

 

EDH

 

Hey,

 

can you place a screenshot from relationships pane that shows all columns of both tables.

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

 Hi Tom

Here is the image

The goal is to show a BAR Chart that will have only the list of business that I have in the List of Food and Grocery (Ignore the typo there)

Thank you very much !

 Capture.PNG

 

 

 

 

PaulDBrown
Community Champion
Community Champion

Just create the bar chart putting the “business name” from the table “List of food and grocery companies” in the x-axis and the “expense” (ie the total debit amount) from the table “List of Debits” in the values field. 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hi

This is the correct solution, because of the relationship between the tables, this forces the pulling of only the terms that apear in the list of debits.

Thank you

EDH

Hey, please excuse but I misunderstood something ...

 

Create a Calculated Column in your table "List of Debits" use this code

Is Related = 
IF(ISBLANK(RELATED('List of Food and Frocery Companies'[Business Name])),"not related", "is related")

This flags the companies in the table "List of Debits" that are also available in the "List of Food and Froceries Companies" table.

 

  • Create a slicer from the column "Is Related" and select "is related"
  • Add the column Business Name" from the "List of Debits" to the axis of your bar chart
  • Add one of the numeric columns to the values field of your bar chart

Done 🙂

 

This will get you started creating Calculated Columns

https://docs.microsoft.com/en-us/power-bi/desktop-tutorial-create-calculated-columns

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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