D365 Snippets & Power BI

Are you new to Power BI and you want to try it out, as a fresher getting a perfect dataset is little difficult, but here Microsoft provides a sample dataset which is already clean, and you can easily use to develop your power bi dashboard reports. The sample is dataset is embedded with Power BI desktop version.

1. Where Sample Dataset Located?

You can easily access the sample dataset by using the path.

Open Power BI desktop version and open the menu Help > Examples > Sample dataset.

2. Load Sample dataset

The first step of dashboard development is, we need to load the dataset to Power BI desktop, for this click on the sample dataset from the path shown above and click Load Samples Data button as shown below.

Our sample dataset contains two data sheets, tick financials sheet, then you can see the data loaded in the preview window, click the Load button. Since the data is already clean, we no need to clean the data again, so we can directly loan the data from dataset to the power desktop.

Once the data fully loaded, you can see one table which is created in the right side of the window under the Data Section as shown below.

3. Create a Date Table

Our sample dataset contains sales related data, so we need to add different search criteria like filter by date period, filter by customer name, filter by nationality etc… the recommended method for filtering with date period is use a date table and map this date table to required table where the data is stored by using the model view window.

Once you created a date table, next step is you need to make a relationship with financial table and date table,

For this, you need to open the model view which is located at the left side of the power bi desktop window, then make a one-to-many relation with date field of date table to the date table of the financial table as shown below.

4. Ad visuals

Once you have completed all the above basic steps, we need to add visuals to the power bi project, here we are adding mainly Card Visuals to show the summary data, Slicers for various filters, donut chart, Column Charts and different bar charts.

1. Slicer

A slicer is a visual filter used to filter the dashboard and display the information only we want. In our project we need to filter the dashboard reports by date, customer name, location, product etc.

For this open the report view tab from the left side and then add four slicer visuals from the visualization tab, these four slicers are used to filter the report with year , month , product and country, then right click on the year slicer and then drag and drop the year column of the date table to the field property of the year slicer , similarly right click on the month slicer and then drag and drop the “Month Name” column of the date table to the field property of the slicer Month as shown below.

For adding Product and Country, drag and drop the fields from financials table, now the slicer set up is ready,

In Visualization Tab, click on the format your visuals > General, the change the Title of the slicer as shown in the figure.

you can see the four slicers are allocated at the top of the dashboard report.

2. Add Card Visuals

In our project we use card visuals to display the summary of total sales, total costs, profit, and the total number of items sold.

For this add four card visuals from visualization tab,

• Right click on the first card visual and then drag and drop the “Sales” column of the date table to the field property of the slicer Month.

• Right click on the second card visual and then drag and drop the “COGS” column of the date table to the field property of the slicer Month.

• Right click on the third card visual and then drag and drop the “Profit” column of the date table to the field property of the slicer Month.

• Right click on the fourth card visual and then drag and drop the “Units Sold” column of the date table to the field property of the slicer Month.

In Visualization Tab, click on the format your visuals > General, then change the Title of the card visuals as shown in the figure.

you can see the four cards are allocated at the top of the dashboard report.

3. Clustered Column Chart

A clustered column chart is used to create a sale by month report, X – axis of the chart will be “MonthName”, and Y-Axis of the chart will be the “Sum of Sales”,

For this add a clustered column chart, the right-click on the chart, drag the “MonthName” column of the financial table to the X-axis of the charts, similarly drag the “Sales” column of the financial table to the Y-axis of the charts.

In Visualization Tab, click on the format your visuals > General, then change the Title of the Clustered Column Chart visuals as shown in the figure.

you can see the clustered column chart is allocated as the first chart in the dashboard report.

4. Clustered Bar Chart

A clustered bar chart is used to create a sale by segment report, X – axis of the chart will be “Sales” and Y-Axis of the chart will be the “Segment”,

For this add a clustered bar chart, the right-click on the chart, drag the “Sales” column of the financial table to the X-axis of the charts, similarly drag the “Segment” column of the financial table to the Y-axis of the charts.

In Visualization Tab, click on the format your visuals > General, then change the Title of the Clustered Column Chart visuals as shown in the figure.

you can see the clustered bar chart is allocated as the second chart in the dashboard report.

5. Donut Chart

A Donut chart is used to create a sale by country report, “Legend” of the chart will be “Country” and “Values” of the chart will be the “Sales”,

For this add a Donut chart, the right-click on the chart, drag the “Country” column of the financial table to the “Legend” of the charts, similarly drag the “Sales” column of the financial table to the “Values” of the charts.

In Visualization Tab, click on the format your visuals > General, then change the Title of the Clustered Column Chart visuals as shown in the figure.

you can see the donut chart is allocated as the third chart in the dashboard report.

6. Stacked Column Chart

A stacked column chart is used to create a sale, cost, and profit report by month report in a single visual, X – axis of the chart will be “MonthName”, and Y-Axis of the chart will be the “Sum of Sales”, “Sum of COGS” and “Sum of Profit”,

For this add a clustered column chart, the right-click on the chart, drag the “MonthName” column of the date table to the X-axis of the charts, similarly drag the “Sales”, “COGS” and “Profit” columns of the financial table to the Y-axis of the charts.

In Visualization Tab, click on the format your visuals > General, then change the Title of the Clustered Column Chart visuals as shown in the figure.

you can see the stacked column chart is allocated as the second chart in the dashboard report.

Finally you can see the output of our sales dashboard project as shown below,

As discussed above here we have four slicers for filtering the charts  with different search criterias, you can choose four slicers values according to your requirement , when we choose the slicer values , you can see the values in the charts and cards will be chaging according the value we filtered in the slicers.

for example , in the month slicer, choose january only , the n the charts will be only shown the data for january only.

Conclusion

Now our project is over, this project is an easy project, because our sample financial dataset is already clean, also we have only two data models here, for more complex projects we may have more data models and need to clean the all the tables also need to make different relation ship to various  models. So as a fresher you can start power bi learning by using this simple Microsoft sample data set.

Useful Tags

How do you Analyse sales data in Power BI?
How do I create a sales report in Power BI?
Does Power BI have dashboard templates?
How do I create a Power BI dashboard example?
Sales Analysis Power BI Dashboard Example
Sales and Marketing sample for Power BI
Top 18 Microsoft Power BI Dashboard Examples 2024
Complete Excellence: Power BI Sales Dashboard
Power bi sample sales kpi template
power bi sales dashboard templates free download
power bi dashboard templates free download pbix
power bi sales report example
sample sales data excel for power bi
sample sales data for power bi
power bi sales dashboard examples
power bi dashboard examples pbix

In this article I will discuss how to work on DAX code using ChatGPT for Power BI, People they are already working on Power Bi will be familiar with DAX functions which are very useful when we work with Power BI for data visualization. This article is very interesting here we will discuss “How to write DAX functions by using ChatGPT for Power BI”.

Usually, it takes a lot of time for mastering in Power BI DAX functions, but by using ChatGPT we will get the DAX functions very quickly and can easily use in our Power BI projects. 

Here we have a Sales Data Set, I used the Sample data set which are already available in Power BI for learning purpose. You can easily access this data set from the below path.

1. Implicit Method

For this Open Power BI Desktop, go to the path.

Help > Examples > Samples dataset > then click Load Sample data.

Then select the financials table and click Load button as shown in the figure.

In the right-side Data Pane, you can see the columns in the financials table.

Here the next step is create a visual which is showing the total sales by country.

For this from the visualization tab, we are selecting a Table visual, then drag and drop the Country and Sales Field from the financials table as shown in the figure. 

Now you can see the total sales value against each country in a tabular view, this method is implicit method, here we no need to take extra steps, we need only drag and drop the columns which are already available in the dataset.

2. Write Measure Using ChatGPT (Explicit Measure)

Next, we can write a measure to find the Sum of total sales by country by using ChatGPT, for this you make a ChatGPT query as shown below.

For this we write a ChatGPT query as shown below.

"I have uploaded one table in Power BI

Table name is financials which has column names Country, Sales

Create a DAX function which calculates Sales by country."

Here "financials" is the dataset and Country, Sales are the table columns which are the required columns to generate the sales by country report.

Copy and paste this query into the ChatGPT as shown below,

Then press enter button, you can see ChatGPT generate DAX function which calculates total Sales by country. Copy the function.

In the Power BI create a measure by clicking the New Measure button under Home > Calculations menu.

Then paste the function, which was copied from ChatGPT as shown below,

Now a measure will be created with name Sales_by_Country, drag and drop this measure to the Table visual as shown in the figure,

Here you can see one column Sales_by_Country is created, which is a calculated column by using DAX function and is called as explicit measure and you can use this measure in another table or anywhere the project.

Conclusion

From the above example you can see , by using DAX function generated by ChatGPT and normal explicit method , in both methods the result is showing as same. This is one of the simplest examples, you can write different DAX functions by using ChatGPT for different purposes, only one thing you need care is you write the correct ChatGPT query which can understand by ChatGPT easily, then only ChatGPT can write correct DAX functions which matches for your project.

If this article is useful, please try to share to your friends and social media. In the next article we will discuss how to write DAX functions by using Microsoft Copilot.

These AI capabilities allow everyone to spend more time on the best parts of their jobs and less time on mundane tasks.

Customer relationship management (CRM) and enterprise resource planning (ERP) systems have long been mission-critical customer and business data sources; however, they frequently require burdensome tasks like manual data entry, content generation, and notetaking.

Dynamics 365 Copilot takes advantage of recent advancements in generative AI to automate these tedious tasks and unlock the full creativity of the workforce. Dynamics 365 Copilot puts CRM and ERP to work for business users to accelerate their pace of innovation and improve business outcomes in every line of business:

• Copilot in Microsoft Dynamics 365 Sales and Viva Sales helps sellers dramatically reduce the time they spend on clerical tasks. AI helps write email responses to customers and can even create an email summary of a Teams meeting in Outlook. The meeting summary pulls in details from the seller’s CRM such as product and pricing information, as well as insights from the recorded Teams call. With sellers spending as much as 66% of their day checking and responding to emails, this presents a significant business upside to give the seller more time with their customers.

• Copilot in Dynamics 365 Customer Service empowers agents to deliver exceptional customer care. Dynamics 365 Copilot drafts contextual answers to queries in both chat and email, in addition to providing an interactive chat experience over knowledge bases and case history so this AI-powered expertise is always available to answer questions. We’re also making it easier for customer service departments to build virtual agents in minutes with conversation boosters in Power Virtual Agents, which harnesses the power of Azure OpenAI Service and Bing to provide answers from company websites and internal knowledge bases that you choose.

• Copilot in Dynamics 365 Customer Insights and Dynamics 365 Marketing empowers marketers to simplify their workflow in data exploration, audience segmentation, and content creation.

• With Copilot in Dynamics 365 Customer Insights, marketers can curate highly personalized and targeted customer segments by having a dialogue with their customer data platform using natural language. Marketers can receive suggestions about additional segments that may not have been previously considered. This new capability can handle complex calculations and match customers that fit a select profile.

• Using Copilot in Dynamics 365 Marketing, marketers can describe their customer segment in their own words to create a target segment with the query assist feature. Marketers can also use Dynamics 365 Copilot to effortlessly get inspiration for fresh email campaign content based on a simple request. Copilot makes suggestions based on key topics entered by the marketer, the organization’s existing marketing emails, as well as from a range of internet sources to increase the relevance of generated ideas.

• Copilot in Dynamics 365 Business Central streamlines the creation of product listings for online commerce. Product attributes such as color, material and size can be used to create compelling product descriptions for online storefronts in seconds. The descriptions can be further tailored by choosing tone of voice, format and length. Business Central customers using Shopify can seamlessly publish the products with descriptions to their Shopify store in just a few clicks.

• Lastly, Copilot in Microsoft Supply Chain Center, which Microsoft Dynamics 365 Supply Chain Management customers can access, will proactively flag external issues such as weather, financials, and geography that may impact key supply chain processes. Predictive insights then surface impacted orders across materials, inventory, carriers, distribution networks, and more. Supply chain planners can then automatically draft an email generated by Dynamics 365 Copilot to alert impacted partners and mitigate potential disruptions before they happen.

The next era of business applications is being transformed by generative AI. Users will increasingly expect their CRM and ERP applications to include AI-powered expertise. Dynamics 365 Copilot brings the latest AI breakthroughs to every line of business, improving customer experience, employee experience, and operational efficiency. Essential to our approach as we bring these latest advancements to customers is our commitment to responsible AI by design – our framework for the safe deployment of AI technologies.

Reference:

Microsoft Blog

LinkedIn Events

Umesh Pandit

Dr.Umesh Pandit expertise in Dynamics 365 for Finance and Operation Administration and Troubleshooting, Microsoft Dynamics AX Infrastructure Support, Dynamics 365 operations on-premise (D365),Microsoft Dynamics 365 Admin,Axapta Administrator,AX Administrator,Microsoft Dynamics AX Deployment, Azure,DMF, Excel Add-In, Ware House, AX Mobile apps, Google Cloud, Microsoft Dynamics ISV,Management Reporter 2012 for Microsoft Dynamics ERP, Microsoft SQL, Microsoft Windows Server, SSRS, TFS, POS, Retail, CRM, Microsoft SharePoint, Hyper-V and System Center. Specialist in Azure, Microsoft Dynamics AX Infrastructure Support, Microsoft Dynamics AX Deployment, Implementations, Upgrades, Migrations, Installation, End User Training, User Training, IT/Admin Training, Pre, and Post Go-Live Support activities.