Measure sales per Retail Category in Power BI

Drill down on sales per category, employee, and department is key essentials for Retailers. Doing this gives a more specific view of what’s generating sales and what isn’t. Having insights into top categories or departments might help make decisions about purchasing and marketing. A good point of sale comes with reporting and analytics, so you can quickly get the data you need, whenever you need it — without manual calculations.

Power BI is a must have for all retailers, and this blogpost is about creating a retail category hierarchy in power BI.

If you have worked with Retail Categories, you know that there exists a “parent-child” relationship between the categories as illustrated from the following data in the Contoso demodata set.

In power BI it is possible to also create such hierarchies, but it requires some minor changes to reflect this. My inspiration came from Power BI Tutorial: Flatten Parent Child Hierarchy. I will not go through how I build a retail power BI analysis, but I can share that I use ODATA entities, and here is the entities I’m using:

More information on the data model is available in DOCS her.

The “trick” is to create a new column named “Path“, and a column named CategoryL[X] for each level in the hierarchy, that for the RetailProductHierarchyCategories looks like this:

Here are the column formulas

Path = PATH(RetailProductHierarchyCategories[CategoryName];RetailProductHierarchyCategories[ParentCategoryName])

CategoryL2 = PATHITEM(RetailProductHierarchyCategories[Path];2)

CategoryL3 = PATHITEM(RetailProductHierarchyCategories[Path];3)

CategoryL4 = PATHITEM(RetailProductHierarchyCategories[Path];4)

CategoryL5 = PATHITEM(RetailProductHierarchyCategories[Path];5)

…etc

Then I create a new hierarchy column for, where I specify

And I use the Hierarchy Slicer that is available in the power BI marketplace.

In power BI I then get a Retail Category slicer, and can filter and measure sales per category in power BI

Microsoft are in process of aligning ourselves with future of Power BI and create the new version of Retail Channel Performance with New Common Data Service for Analytics capability coming to Power BI https://powerbi.microsoft.com/en-us/cds-analytics/

Keep on rocking #MSDYN365FO!

Failed ERP implementation will change partners to become trusted advisors.

A norwegian customer won a compensation case against an ERP implementation partner after the customer terminated the parties’ agreement on the supply of a new ERP. The customer was compensated by the Norwegian district court assessed at 288 mNOK (36,7 mUSD). Originally the contract was worth 120 mNOK. You can read the complete story here http://www.selmer.no/en/nyhet/felleskjopet-agri-wins-district-court-case. The court decision is expected to be appealed.

Luckily this was NOT a Dynamics 365 implementation, and the customer is actually replacing the failed ERP system with Dynamics 365. The reason why I wanted to write about this story is that it has implications on how much risk and responsibility an ERP implementation partner can take. A major part of the ERP partners are smaller companies with less than 100 employees, than cannot take the risk of getting into such a situation. There are always problems and risks that is beyond what a ERP partner can control. Partners are not the developer company of the standard software. They are implementing, and in some cases adding additional extensions. Also the cloud based software are running on azure that is beyond the control of the partner.

How can this change partners behavior? Partners are changing towards becoming verticalized trusted advisors, but with limited responsibilities. We can give recommendations based on what we know about the software and how to use it efficiently but the costs are more on a T&M(Time and Material) basis. It will more be the customer them selves that is responsible for the implementation and time-tables.

Some customers will not accept this change, but other do. There are currently resource constrains in the Dynamics 365 partner channel and we partners avoiding customers that takes a back-seat approach towards their implementation projects. The sales focus will change towards those customers that take more of the responsibility themselves, and that do understand to take a more dynamic and agile approach. A 400-page requirement document is not a good start for an ERP project, as we see the digitalization possibilities are accelerating. We also see that customers don’t run a 2 year ERP implementation project before going live. They run a 90 days project to get live with only parts of their requirements. The project then takes on other areas and they extend their use of the Dynamics 365.

At the end, I include some trusted advisor recommendations that I think can inspire anyone that is about to start a project.