Skip to Content
0
Jun 29, 2020 at 05:07 PM

Configuration of the measures in the model

70 Views

Hi everyone,

I would like to know how to display in a table or a graph a price, an unit cost according to the store and the type of article, this without aggregation like SUM.

In my model, we have in the Account dimension some measures like :

  • Quantity
  • Cost price
  • Amount.

The dimensions used as axis of analysis are :

  • Period (date dimension)
  • Store (organization dimension), with a single geographical hierarchy including city and country
  • Article(generic dimension), with two hierarchies.

In the story, I have created a cross-tabulation showing the cost price by article and store location, but the cost price is inaccurate.

In the model:

In the story:

The cost is indeed too high and does not correspond to what we have in the raw dataset.

The cost is also not the same for the same article in two stores located in the same country (e.g. Item "ENFANTBRACELET" in the city of Florence costs €879.11 while in the city of Milan the same article costs €1163.88), while in the raw dataset both cities are supposed to show a cost price of €6.36 since they are both located in Italy).

Here are the costs in the raw dataset.

In the Account dimension (in the modeler view), I tried to set up the costing in this way:

  • AVG in the "Exception Aggregation Type" column
  • The dimensions Article and Store in "Exception Aggregation Dimension" column.

This gives us this result on the story, but that's still not the desired result.

What is the most appropriate account type for this type of measure?

In order to show only the gross value of the cost per item and storage location (and not apply a SUM type aggregation), is it necessary to apply a specific aggregation exception?

Kind regards,

Attachments

screen3.png (32.8 kB)
screen4.png (18.9 kB)
screen5.png (39.1 kB)
screen2.png (60.6 kB)
screen3.png (43.3 kB)
screen1.png (9.8 kB)