How to relate a table of objectives for commercials and product families to a sales fac table

[Osky]

Hi everyone, I was hoping someone could give me a hand or another approach to my problem statement.

I have a sales table that has a standard look like this one:

I want to set an objective table for the family of products that every comercial sells each trimester.

Here is an example of how my table looks like.

At the end I'd like to return a matrix that summarizes all the sales done for each comercial, and family product, and compare them with the objectives assigned and the sales he made in the same period last year.

Something like this:

My first approach was to relate them by a calendar table and use year-trim as a principal relationship, although it was a many to many relationship seemed to work, but I found out I cant separate sells by a product's family nor commercial.

For the measures, I think I can solve them alone, yet I can't establish the correct relationship between all dimensions.

I think that a simpler approach should be done, yet I can't figure it out.

Any help is welcome, many thanks for the attention.

[v-shex-msft · Accepted solution]

Hi @Osky,

In fact, you not need to mapping with too many fields, they will affect the records aggrations.

In my opinon, I'd like to suggest you reduce the concatenated relationship key fields, you can use the year quarter and category fields as relationship key to mapping table.

For the detail level records , you can add more detailed category fields to the visual and these records will auto aggregate by current relationships key mappings.

Regards,

Xiaoxin Sheng