cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hidenseek9
Helper V
Helper V

Filtering data and making a graph in Power BI

Hi, Power BI Community

 

I am having a problem with Power BI.

 

My objective: To filter the data loaded in Power BI and create a graph based on the filtered data

 

To explain more, please find the attached dummy data.

Dummy Data

 

In this file, I have following items.

  • Document Type
  • Document Number
  • Value
  • Date
  • Vendor

What I would like to do is to create a graph with  

  1. Y-axis: Total value with Document Type (RE and WE)
  2. X-axis: Date (If there are multiple document types for the same document number, take the earlier Posting Date and add 60 days to that date)

I want to show this in a monthly view, so I know how much I paid each month for which vendor.

 

Appreciate your support!

 

H

 

 

6 REPLIES 6
v-huizhn-msft
Microsoft
Microsoft

Hi @hidenseek9,

First, you can filter the data by Query Edit and only load the Document Type RE and WE. I test using part of your sample table.

Click Edit Query-> Right click the Document Type header->select RE and WE->Close&Apply, you will filter resource table successfully. 

1.PNG

You can create a marix visual. select the Date as axis level, Document Type as column, Document Number as value, please see the following screenshot.

2.PNG

>>I want to show this in a monthly view, so I know how much I paid each month for which vendor.

I don't quite understand your reqirement, could you please describe it clearly?

Best Regards,
Angelia


@v-huizhn-msft

 

Thank you for your reply, Angelia.

Filtering document type in Query Edit certainly works.

 

In a visual, I would like to have just like below screenshot.

Shows the amount in a monthly view.

 

2017-04-28 16_09_00-CashFlow Test1 - Power BI Desktop.png

 

Then, I would like to go have a breakdown by vendors like below.

I would like to have a hierachy set up so that if I double click on January bar,

it will drill down to every single PO for January.

 

2017-04-28 16_11_55-CashFlow Test1 - Power BI Desktop.png

 

 

I am struggling with adding value for a PO that has both document type RE and WE in data and

then have another set of data that shows the earlier posing date of type RE and WE for the PO.

 

>>I want to show this in a monthly view, so I know how much I paid each month for which vendor.

I don't quite understand your reqirement, could you please describe it clearly?

=>This just means that I would like to have a graph with a monthly view just like above and to be able to see at vendor level as well as PO level. The date should be the earlier of the posting date of document type RE or WE for the same PO number.

 

If you could support me on this, that would be fantastic!

 

Many thanks,

 

H

Hi @hidenseek9

For your second picture, please create a stacked column chart ane select the Vendors field as Legend, the Month as Axis level, the value as value level.

Best Regards,
Angelia

@v-huizhn-msft

 

Thanks for the reply.

I already know how to use a graph, actually.

What I need a help with is for Posting Date.  

 

For a PO that has a multiple document type (RE, WE, etc.),

I would like to have a column in Power BI data to show the Posting Date of Document type WE.

 

Hope you can help me with this.

 

Many thanks,

 

H

 

Hi @hidenseek9,

Sorryfor  still confusing the setence "I would like to have a column in Power BI data to show the Posting Date of Document type WE". You want show the RE,WE value on the visual? If it is, you can set the Date labels on.

1.PNG

Thanks,
Angelia

@v-huizhn-msft

 

Thank you for your reply.

Hope below data and screenshot will clarify everything.

 

In below Dummy Data, you will see 3 tabs.

  1. Pivot
  2. Raw
  3. What I want

Dummy Data

 

In Raw tab, I have just raw data and in Pivot tab I have a pivot table of the raw data.

In Pivot, you will see items highlighted in yellow just as below.

 

2017-05-06 09_45_10-Dummy Data.xlsx - Excel.png

 

These items have the same document number but multiple document types (such as RE and WE)

These items have different Posting Dates.

For example, for Doc number 13245, RE has Posting Date of 2015/3/10 and WE has Posting Date of 2015/2/16.

 

What I would like to see is that for document type RE, instead of 2015/3/10, I want it to be 2015/2/16,

the same date as Document type WE.

 

I showed another example in What I want to See tab, just as below.

 

2017-05-06 09_43_41-Dummy Data.xlsx - Excel.png

 

What I want is, in What I Want to See tab (within Power BI) that

in column F, I would like to show Posting date, but for Doc type other than WE, to show

Posting date of WE.

In above case, for Doc number 13145, Doc type RE to show the Posting date of 2015/1/28,

instead of 2015/4/28.

 

Hope above clearfies everything.

 

Many thanks,

 

H

 

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors