DAX Sum of max by group of selected by filters and overall

[VincentC]

Hi,

I'm pretty new to PBI and I'm facing a problem.
To explain it easily, I'll first describe my tables and their relations, I've also prepared some dummy data as
like most people I can't share the real ones.

So, I've 3 main tables (I've other ones too but don't think they would have an impact here):

And this kind of data: 

What I'm trying to achieve is:

Be able to select a shop and a date, and for this selection display something like:

Group by Category, Subcategory, Max(Number_y);

and the others selection:

Group by Category, Subcategory, Sum(max_y) from
Group by Shop, Date, Category, Subcategory, Max(Number_y) as max_y

The goal is to do this also for the Number_x and get:

by category, subcategory ( selected_y/selected_x - notselected_y/notselected_x ) / ( notselected_y/notselected_x )


I've already been able to do this with two slicers, not related to any table, and those measures:

for the shop and date selected:

VAR _DATE = SELECTEDVALUE('Unrelated_table'[date])
VAR _SHOP = SELECTEDVALUE('Unrelated_table'[shop])

SUMX(
    FILTER(
        SUMMARIZE(
        'Products'; 'Products'[shop]; 'Products'[date]; 'Products'[category]; 'Products'[sub_category]
        );
        'Products'[date] = _DATE && 'Products'[shop] = _SHOP
        );
        CALCULATE( MAX( 'Products'[Number_y] ) )
        )

for the rest:

Same as the previous one, without FILTER() minus the previous one.
 

       
And sure this works great.

But I've also others vizualisations that I'd like to filters by the selection of the shop and the date without success using the unrelated slicers.
(for example: I display the topN -value of N based on a slicer- order_number and would like to display the topN overall if no shop and date are selected and the corresponding ones if something is selected). Using unrelated slicers will make the conditions pretty complicated if in the future I add more slicers.

Regards,

Vincent

[VincentC]

Unfortunatly my internet access is pretty restricted at work, so I'll put the dummy data as csv in code block. Sorry for the inconvenience.

Master:

Shop;Date;Order_number;Category;Number_y
A;01/01/2018;1;C1;10
A;01/01/2018;2;C2;250
A;01/01/2018;3;C1;40
A;01/01/2018;4;C3;90
B;01/01/2018;1;C1;25
B;01/01/2018;2;C2;950
B;01/01/2018;3;C1;50
B;01/01/2018;4;C3;230
A;01/02/2018;1;C1;15
A;01/02/2018;2;C2;1100
A;01/02/2018;3;C1;20
A;01/02/2018;4;C3;190
B;01/02/2018;1;C1;35
B;01/02/2018;2;C2;1350
B;01/02/2018;3;C1;25
B;01/02/2018;4;C3;225

Products:

Shop;Date;Order_number;Sub_category
A;01/01/2018;1;Y
A;01/01/2018;1;Z
A;01/01/2018;2;X
A;01/01/2018;3;W
A;01/01/2018;3;X
A;01/01/2018;3;Z
A;01/01/2018;4;V
A;01/01/2018;4;Y
B;01/01/2018;1;Y
B;01/01/2018;1;Z
B;01/01/2018;2;X
B;01/01/2018;3;W
B;01/01/2018;3;X
B;01/01/2018;3;Z
B;01/01/2018;4;V
B;01/01/2018;4;Y
A;01/02/2018;1;Y
A;01/02/2018;1;Z
A;01/02/2018;2;X
A;01/02/2018;3;W
A;01/02/2018;3;X
A;01/02/2018;3;Z
A;01/02/2018;4;V
A;01/02/2018;4;Y
B;01/02/2018;1;Y
B;01/02/2018;1;Z
B;01/02/2018;2;X
B;01/02/2018;3;W
B;01/02/2018;3;X
B;01/02/2018;3;Z
B;01/02/2018;4;V
B;01/02/2018;4;Y

Infos category:

Shop;Date;Category;Number_x
A;01/01/2018;C1;150
A;01/01/2018;C2;3000
A;01/01/2018;C3;700
B;01/01/2018;C1;300
B;01/01/2018;C2;4500
B;01/01/2018;C3;850
A;01/02/2018;C1;150
A;01/02/2018;C2;3600
A;01/02/2018;C3;450
B;01/02/2018;C1;300
B;01/02/2018;C2;5000
B;01/02/2018;C3;800

[erikajain02]

Please upload Dummy data in xl or csv format so that we can try and provide you solution.