Forum Discussion

hidenseek9's avatar
hidenseek9
Icon for Post Patron rankPost Patron
9 years ago

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

 

 

Need Help

6 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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. 



    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.



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


    • hidenseek9's avatar
      hidenseek9
      Icon for Post Patron rankPost Patron
      9 years ago

      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.

       

       

      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.

       

       

       

      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

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        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