Get stock by color - 1 to 1 relationship

[rolo]

Hello gurus!

WHAT I HAVE

I have a power pivot model with 3 tables:

1. Product (product key field with no duplicates)
2. Stock (product key field with no duplicates)
3. Sales (transactions table, many ocurrences of product key)

Then I have my relationships:
One to many relationship from Product to Sales Table (using ProductKey field)
One to many relationship from Stock to Sales Table (using ProductKey field)

WHAT I NEED

I need a pivot table to show stock by color. But when I do it I get this result:

Then I have tried 1 to 1 relationship but it is not possible.

Then I have tried adding a new "color" column in sales table, using this formula:
=related(Product[Color]), but when I build my pivot table with that new color field, I get same result (which repeats same stock for each color).

Is there any solution or any other DAX to be able to show stock by color?

I use Excel 2016 - download example here: https://dropfile.to/TJr79CP

Thanks!

FINAL NOTE
If I do the same using Power BI desktop I get the correct result, because it allows 1 to 1 relationship (Product[Color] to Stock[Stock]). But I need to use Excel 2016

 

[GraH - Guido]

Hi Rolo,

I could not get your file since it was expired, but I believe I made it work like this:
1. change relationship from Sales to Stock -> Product to Stock
2. add the measure in the product table with this formula=sumX(DimProductTable;RELATED(DimStock[Stock]))


In the Pivot it comes out like this (the first measure is a SUM based on a calculated column, also using related function)


I also believe this works in PowerBI without changing the one to many relationship.

kr
Guido

 

[rolo]

Thanks for your help Guido, but I could not make it work.
Here you can download the example file again: https://dropfile.to/uVpmNvA
Can you check it out? Thanks!

 

[NARAYANK991]

Hi ,

This is just a question : why do you have a separate table named Stock when all it contains is one field named Stock ? Why can this field not be a part of the Product table ? After all , if a Product does not have any stock , the Stock field can have 0.

I understand that the number of records in the Stock table may be much less than the number of records in the Product table.

Narayan

 

[GraH - Guido]

Hi ,

This is just a question : why do you have a separate table named Stock when all it contains is one field named Stock ? Why can this field not be a part of the Product table ? After all , if a Product does not have any stock , the Stock field can have 0.

I understand that the number of records in the Stock table may be much less than the number of records in the Product table.

Narayan

It sure would make life easy!

 

[GraH - Guido]

Hi Rolo,

Can you use the COLOR field from the table product and not from the table sales.
I have removed a lot of columns from the data in order to enable the upload (max 1 mb allowed), but create the model as in this example and make the explicit dax measure in the same way.

Cheers
Guido

 

[GraH - Guido]

Hi Rolo,

Even simpler I think. Replicate the product key (calculated column) in the table products. Link this 2nd key to the product key fields in the stock table. I never saw a PP data model where a single column was linked to 2 separate lookup tables. But doing it like this with a replicated key, the data model relationship is not ambiguous and this relationship will work fine in the given filter context.
Drag color field from the product table in the row labels and drag stock field from the stock table in the measures. I expect this should work like a charm.
Now bare in mind, that it is advised to always create explicit DAX measures.

Kr
Guido

 

[Chihiro]

Hmm, I take it you are loading data directly into Data Model via PowerPivot Wizard?

I'd suggest, you go through PowerQuery and then load to Data Model. PowerQuery should be used to clean, shape and transform the data, and PowerPivot to model relationships and perform calculations.

Consider creating star schema with single fact table connected to multiple dimension tables.

https://en.wikipedia.org/wiki/Star_schema

Also have a read of link below.
https://powerpivotpro.com/2016/03/power-pivot-versus-power-query-power-bi-dilemma/

 

[GraH - Guido]

Hmm, I take it you are loading data directly into Data Model via PowerPivot Wizard?

I'd suggest, you go through PowerQuery and then load to Data Model. PowerQuery should be used to clean, shape and transform the data, and PowerPivot to model relationships and perform calculations.

Consider creating star schema with single fact table connected to multiple dimension tables.

https://en.wikipedia.org/wiki/Star_schema

Also have a read of link below.
https://powerpivotpro.com/2016/03/power-pivot-versus-power-query-power-bi-dilemma/

Hi Chihiro, spot on, but Rolo's data looks very familiar (MS contoso DB), so the data is kind of ok/standardized and I don't see the need for PQ in this case, though I used PQ to clean-up a lot of columns from the model to enable the file upload here.
Anyways, I just tried to solve the issue, but you and NARAYANK991 are both right that Rolo's data model is not optimal. Though in this case linking a lookup table to a second lookup table is not a disaster, given the limited amount of data and tables. I noticed it is also the case in the MS PP tutorial based on the contoso DB.