Sum depending on dynamic top

[TKH8]

Hello,

I have a problem that seemed easy to solve but i'm blocked.

I have a data source with 4 fields :

Network 

Shop (Shop is linked to a Network).

Sales Type 

Sale (equal to one).

Im trying to have a matrix wich display the Top 4 Sales Type with the associated number of sales with the shop as a segment.

I succeed to do it easily but now i would like to show in the matrix the number of sales by network for this sales type (the 4 that compose the top 4 for the selected Shop).

To ease the process I created a table that precalculate the Sales Type Top 4 for each Shop.

A simplified version of the source (in the original report I have more segments, year, month, Shop).

I created 4 objects to identify the Top 4 based on the Selected_Shop 

Top1_Type_select = CALCULATETABLE(DISTINCT(Tops[Top1]), FILTER(Tops,Tops[Shop]=[Selected_shop]))
Top2_Type_select = CALCULATETABLE(DISTINCT(Tops[Top2]), FILTER(Tops,Tops[Shop]=[Selected_shop]))
Top3_Type_select = CALCULATETABLE(DISTINCT(Tops[TOP3]), FILTER(Tops,Tops[Shop]=[Selected_shop]))
Top4_Type_select = CALCULATETABLE(DISTINCT(Tops[Top4]), FILTER(Tops,Tops[Shop]=[Selected_shop]))

And an object to get the total sales by type

Total_top_sales = calculate([Count], FILTER(Sales,Sales[Sales Type]=Tops[Top1_Type_select]))+calculate([Count], FILTER(Sales,Sales[Sales Type]=Tops[Top2_Type_select]))+calculate([Count], FILTER(Sales,Sales[Sales Type]=Tops[Top3_Type_select]))+calculate([Count], FILTER(Sales,Sales[Sales Type]=Tops[Top4_Type_select]))

Table Sales (sample)

NETWORK	SHOP	Sales Type	Count
Net1	Shop1	Type7	1
Net1	Shop1	Type7	1
Net1	Shop1	Type5	1
Net1	Shop1	Type1	1
Net1	Shop1	Type12	1
Net1	Shop1	Type7	1

Table Top

Network	Shop	Top1	Top2	Top3	Top4
Net1	Shop1	Type7	Type8	Type14	Type11
Net2	Shop10	Type13	Type4	Type12	Type9
Net1	Shop2	Type8	Type6	Type13	Type1

Matrix target

	Type	Shop3	Network	Total
Top1	Type11	7	21	23
Top2	Type1	6	19	24
Top3	Type12	5	20	32
Top4	Type9	4	13	22

Thanks in advance to the kind soul that will help me

Regards 

Louis

[v-easonf-msft]

Hi, @TKH8 

The results shown by the 'Matrix target' confuse me even more.

Can you show a sample file to further illustrate your question?

Best Regards,
Community Support Team _ Eason