Prefered SKU per posname with conditions

[Zaoka]

HI,

I'm trying to combine two tables with power pivot to see prefer SKU per Posname according to State and RetailCategory, but I'm doing something wrong

1. table is Base with columns Posname, Entity, RetailCategory, Sales person ID
2. table is prefer SKU with colmns Entity,RetailCategory, SKU name, days

My result should be single Power pivot table in tabular form

Posname, Entity, RetailCategory, Sales person ID, SKU name, days

I created a helper column in both tables with a combination of State & RetailCategory as single column and "key" table with all combination of State & RetailCategory . Then I load it all to power pivot , created relation and result is wrong , I simply get all possible SKUs and states per posname

Example excel google drive 

[v-lid-msft]

Hi @Zaoka ,

Sorry for our mistake, we checked again find the logic in the third excel sheet, we change the value of matrix to distinct count of sku and get the expected result 79:

Best regards,

[v-lid-msft]

Hi @Zaoka ,

Does this issue have been resolved?Could you please provide more details about it If it still not be resolved? Please don't contain any Confidential Information or Real data in your reply.

Best regards,

[v-lid-msft · Accepted solution]

Hi @Zaoka ,

We can create two dimision tables to meet your requirement:

Entities = DISTINCT(UNION(DISTINCT('Base'[Entity]),DISTINCT('Prefered SKU'[Entity])))

Categories = DISTINCT(UNION(DISTINCT('Base'[RetailCategory]),DISTINCT('Prefered SKU'[RetailCategory])))

Then we can use them in matrix visual to get the SKUs for postname:

But for each certain postname, they may have multi SKUs, such as for postname 000008, could you please share the logic you want to get teh prefered SKU if the above matrix cannot meet your requirement?

Best regards,