kurthatlevik: Measure sales per Retail Category in Power BI

[Blog bot]

Источник: https://kurthatlevik.com/2018/05/21/...y-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 <a href="https://powerbi.microsoft.com/en-us/cds-analytics/">https://powerbi.microsoft.com/en-us/cds-analytics/

Keep on rocking #MSDYN365FO!



Источник: https://kurthatlevik.com/2018/05/21/...y-in-power-bi/