{"id":36461,"date":"2021-03-14T18:43:20","date_gmt":"2021-03-14T13:13:20","guid":{"rendered":"https:\/\/techmasala.addastudents.com\/dev\/?p=36461"},"modified":"2021-03-15T10:35:15","modified_gmt":"2021-03-15T05:05:15","slug":"sales-report-in-power-bi","status":"publish","type":"post","link":"https:\/\/techmasala.addastudents.com\/dev\/sales-report-in-power-bi\/","title":{"rendered":"Design Sales Report in Power BI"},"content":{"rendered":"\n

Lets get started and design a Sales report in Power Bi. I have used Dynamic 365 Finance and operation data entities as the data source, you can use any other mode of data as per your requirement.
Below are the steps for getting data and designing data in a report format<\/p>\n\n\n\n

For connecting your D365 data source to Power bi, first move to your power bi desktop and specify dynamics url concatenated with “\/data”.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n

Click connect
 <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Make a connection by signing in after making a connection you will be able to access all the dynamics 365 finance and operation entities , you can choose multiple which you need in  your report. The preview of the data will be available of the choosen data source.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

There is two option to load, transform or cancel data.
You can choose load data to exact load all the data.
You can choose transform data when you want to apply filters to your data source.
 <\/p>\n\n\n\n

Let’s get ahead, and design a report .. As in the above steps you have made the connection to the data source , now let’s design the Data in the understandable format.<\/p>\n\n\n\n

Below is the Revenue Trend Report ..<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n

Above highlighted data sources are used to design above report.
These are the data entities of D365 finance and operation.<\/p>\n\n\n\n

Let’s divide the report in two parts filters and dashboards visual.<\/p>\n\n\n\n

Firstly we will work on the filters as in dashboard we are using all the filters .

Filters<\/strong><\/p>\n\n\n\n

  1. Financial Year :
     <\/li><\/ol>\n\n\n\n
    \"\"<\/figure>\n\n\n\n

          As above, all the financial year are visible in the filter.<\/p>\n\n\n\n

    This filter is designed on the basis of the date. For doing so, we have created a custom column in which data is filtering on the calculation defined.
    Move to your data source and right click on new column option highlighted below.
     <\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    Below highlighted window will appear.<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    As you can see, you can filter data, you can change type and you can write your custom code that will appear in that column.
    I have named the column to financial year.<\/p>\n\n\n\n

    Below is the query I have used.<\/p>\n\n\n\n

    IF(MONTH([Date])>3,YEAR([Date])&”-“&YEAR([Date])+1,YEAR([Date])-1&”-“&YEAR([date]))<\/p>\n\n\n\n

    Syntax: If(condition, true, false)<\/strong><\/p>\n\n\n\n

    In the above calc. we have return ( current year – next year) when the month is greater then three else return (previous year –  current year)<\/p>\n\n\n\n

             After creating column click on new visual and change its visualization to slicer, highlighted                                                                            below.<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    In the field select your required field i.e. financial year.<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    By default it is in list format to make it dropdown , click on the arrrow highlighted on the right side of the design, as shown below .<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    Select dropdown.
     <\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    It will be modified as above.
    for changing its visualization i.e. its color, background, font etc. Walk through the visualization section, as shown below.<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    Final output :<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    For creating rest of the filters you just need to assigned fields.<\/p>\n\n\n\n

    \n
    \n
    \n
    \"\"<\/figure>\n<\/div><\/div>\n<\/div>\n<\/div>\n\n\n\n

    Dashboards:<\/strong><\/p>\n\n\n\n

    1.Quarter wise revenue trends:<\/span><\/strong><\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    The above data basically shows the quarter wise Amount of sale.
    For designing this follow below mentioned steps.<\/p>\n\n\n\n

    1. First select new visual as bar chart.<\/li><\/ol>\n\n\n\n
      \"\"<\/figure>\n\n\n\n

      As we need data quarterly, we will add quarter to the axis.
      Note: Date in Bi are stored in hierarchy format. You can get year quarter month and day.<\/p>\n\n\n\n

      \"\"<\/figure>\n\n\n\n

      After adding quarter add your value, I have used line Amount field of Sales Invoice line entity.<\/p>\n\n\n\n

      \"\"<\/figure>\n\n\n\n

      For changing labels and formatting design, walk through visualization as shown below.<\/p>\n\n\n\n

      \"\"<\/figure>\n\n\n\n

      2. Revenue trend YOY<\/span><\/strong><\/p>\n\n\n\n

      \"\"<\/figure>\n\n\n\n

      This report basically gives revenue month wise and as per financial year.<\/p>\n\n\n\n

      For creating this follow below steps.<\/p>\n\n\n\n

      1. Firstly select new visual and then select line chart.<\/li><\/ol>\n\n\n\n
        \"\"<\/figure>\n\n\n\n

        For Axis add month , and financial year at legend and line amount at value<\/p>\n\n\n\n

        \"\"<\/figure>\n\n\n\n

        For changing labels and formatting design,  Walk through the visualization section, as shown below.<\/p>\n\n\n\n

        \"\"<\/figure>\n\n\n\n

        3. Sales Person Wise Revenue<\/span><\/strong><\/p>\n\n\n\n

        \"\"<\/figure>\n\n\n\n

        This report basically gives over view of sales person wise quarter wise revenue generated.
        For creating so follow the below mention steps.<\/p>\n\n\n\n

        1. Firstly select new visual and select Stacked Area Chart.<\/li><\/ol>\n\n\n\n
          \"\"<\/figure>\n\n\n\n

          Add Sales person to axis, quarter to legend, line amount to values.<\/p>\n\n\n\n

          \"\"<\/figure>\n\n\n\n

          For changing labels and formatting design,  walk through the visualization section, as shown below.<\/p>\n\n\n\n

          \"\"<\/figure>\n\n\n\n



          Note:<\/strong> Enabled cross report filtering to filter all the report as per the filter.<\/p>\n\n\n\n

          \"\"<\/figure>\n\n\n\n

          Hope the above helped you out in designing the report and getting started to Power BI. If there any queries feel free to contact us and comment below.

          You can also let us know with what you want next in power bi, we will try level best to do so.

          For post like this visit our facebook Page<\/a> and YouTube <\/a>channel.

          <\/p>\n","protected":false},"excerpt":{"rendered":"

          Lets get started and design a Sales report in Power Bi. I have used Dynamic 365 Finance and operation data entities as the data source, you can use any other mode of data as per your requirement.Below are the steps for getting data and designing data in a report format For connecting your D365 data […]<\/p>\n","protected":false},"author":1,"featured_media":36466,"comment_status":"open","ping_status":"open","sticky":true,"template":"","format":"standard","meta":{"footnotes":""},"categories":[758,72],"tags":[614,613,615,606,610,611,602,603],"class_list":["post-36461","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-learn-microsoft-powerbi","category-tech","tag-power-bi-administration","tag-power-bi-app","tag-power-bi-architecture","tag-power-bi-certification","tag-power-bi-course","tag-power-bi-interview-questions","tag-power-bi-jobs","tag-power-bi-tutorial"],"_links":{"self":[{"href":"https:\/\/techmasala.addastudents.com\/dev\/wp-json\/wp\/v2\/posts\/36461","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techmasala.addastudents.com\/dev\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techmasala.addastudents.com\/dev\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techmasala.addastudents.com\/dev\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techmasala.addastudents.com\/dev\/wp-json\/wp\/v2\/comments?post=36461"}],"version-history":[{"count":9,"href":"https:\/\/techmasala.addastudents.com\/dev\/wp-json\/wp\/v2\/posts\/36461\/revisions"}],"predecessor-version":[{"id":36525,"href":"https:\/\/techmasala.addastudents.com\/dev\/wp-json\/wp\/v2\/posts\/36461\/revisions\/36525"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/techmasala.addastudents.com\/dev\/wp-json\/wp\/v2\/media\/36466"}],"wp:attachment":[{"href":"https:\/\/techmasala.addastudents.com\/dev\/wp-json\/wp\/v2\/media?parent=36461"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techmasala.addastudents.com\/dev\/wp-json\/wp\/v2\/categories?post=36461"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techmasala.addastudents.com\/dev\/wp-json\/wp\/v2\/tags?post=36461"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}