Creating Pricing Cube Workview

Let’s take a look at the new Pricing cube and create our planning template for Pricing. For this template it would be good to see the Retail price and Cost price for each product, along with the override measure for each of these. This workview will be named Price Planning.

We will open our Pricing cube and rearrange the dimensions as shown in the image.

Now go into the Set Editor for Scenario and Year, adding a Expand All instruction to both and save. In the dropdowns select Actual as the scenario and FY2017 as the year.

Double-click on the highlighted cell and choose All Products hierarchy, expand and drag all the products shown under and click on Add to Selection Instructions. Then remove the existing instructions to depict the image shown and hit Save.

Double-click on the highlighted cell and choose the Quarters hierarchy. Remove the existing instructions and add Annual to the Set Instructions by double-clicking on it. Then, add the Add Blank Space instruction.

Choose Default hierarchy and expand All Months. Now drag and select all the months under it and click on Add to Selection Instructions and hit Save.

We will have the starting price for the year stored in the Annual month element,and we will write a formula in the next step which pushes this into the months.

Select Budget from Scenario and the Year as FY2018 from the dimension filters at the top.

Enter 40 as the value against the cell at the intersection of Annual,Pencil,Retail Price.

To create the formula, right-click on the target cell (highlighted in yellow) and select Edit Formula. Set the description as Retail and Cost Price - Jul. We will restrict the formula to the Budget scenario and Jul month and Don't Restrict on the measures (restrict it as shown in the image).

HASSTATICVALUE() is function that checks if the current cell has a manually input number, and returns a boolean value.

The formula will apply to Retail Price and Cost Price and will take the value of Annual if Jul has no value.

IF( HASSTATICVALUE(),
    CONTINUE,
    ["Annual"]
)

To add the Rollover formula, right-click on the target cell (highlighted in yellow) and select Edit Formula. Set the description as Rolling Override. We will restrict the formula to the Budget scenario and Lowest elements in the Month List hierarchy and Don't Restrict on the measures (Restrict it as shown in the image).

Values will roll over to the next month. However, if the cell has a manually input, that number will show instead.

IF( HASSTATICVALUE(),
    CONTINUE,
    SEQUENCE("Month", "Month List", -1,[])
)

Enter 26 as the value against the cell at the intersection of Annual, Pencil, Cost Price.

You will notice that 26 will roll over showing 26 in all months.

Save this Workview as Price Planning

---

We haven't added the actual prices into the cube yet, so let’s create a view in which we can paste our current product pricing for the year.

From our model page, open the Pricing cube.

Double-click on the highlighted cell, remove Enable Drill Down and add Expand All and hit Save.

We will repeat what we did on the previous workview and go into the Set Editor for Scenario and Year, adding a Expand All instruction to both and save. In the dropdowns select Budget as the scenario and FY2018 as the year.

Click on the highlighted icon to the Month filter. Then select Annual under the Quarters hiewrarchy and add it. Then remove existing instructions.

From the Office Supplies Data file (named "Annual Price Sheet FY18 - Office Supplies Co.xlsx", copy the content and paste it with the highlighted cell as the selection using Control + V keys.

Once pasted, we can see the values being aggregated in the workview. Please note that we did not remove the manual override values that we entered earlier.

Save this Workview as Annual Price Entry