Beginner filtering question

[edh]

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

[TomMartens]

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

[edh]

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

[TomMartens]

Hey,

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

Regards

Tom

[edh]

 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 !

 

[PaulDBrown · Accepted solution]

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. 

[edh]

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

[TomMartens]

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