Recreate % Parent Row Total with Dax in Pivot Table (not show value as)

[Menar]

Hi everyone,

I am new to DAX and still on the learning path so I might have missed something obvious. 
I am linking a dummy file which replicates the situation of my real file on this link:

https://www.dropbox.com/scl/fi/avwflsysl0a0dfdkn0id2/Parent-Row-Total.xlsx?dl=0&rlkey=ia54co1dl0fqkr...

I am working with simple sales data which I sum up in a pivot table in Excel.

For the Filters section I have the "Sales Contract type" field.
For the Columns section I have "Year" field, as I am looking at the evolution of our company's sales year to year.
For the Rows section I have 3 fields, "Country", "Product", "Product Pack" that I rearrange in any order depending on the need.

For the Values section I have the "Sales" field. I need to show the market shares and market share variation in points
To do so, I select show values as % parent row total which gives me market shares.
Now, ideally I would drag the "Sales" field again into the Values section and select Show value as Difference from to have a variation in points year to year. Yet, the pivot table does not consider the first instance of the field "Sales" as real percentages its just a formatting so difference from returns absolute growth in sales (basic substraction). 

So i am trying to recreate the % of parent row total with DAX to have real percentages and then apply "show values as difference from" to the result of this DAX measure.

I've done some extensive research on this already and most of the time people can do this but only if the fields' order does not change. I need to be able to move the fields in the rows section in any way I want and still have % of parent row total.

First, I have been able to recreate the % of Column Total with this measure (could be useful):
=DIVIDE([Sum of Sales]
;CALCULATE([Sum of Sales]
;ALLSELECTED()
;Values(Range[Year])))*10000

In my attempts to recreate % of Parent Row Total I've come up with a first measure:
Parent Sum=
=IF (
ISFILTERED ( Range[Product Pack] );
CALCULATE (
SUM ( [Sales] );
ALL ( Range[Product pack] )
);
IF (
ISFILTERED ( Range[Product] );
CALCULATE (
SUM ( [Sales] );
ALL ( Range[Product] )
);
CALCULATE (
SUM ( [Sales] );
ALL ( Range[Country] )
)
)
)

And then a second measure using the result from the one above:
Replica of % Parent Row Total=
Sum(Range[Sales])/[Parent Sum]*10000

This does indeed work BUT only if the fields in the Rows section are organized in the reverse ordered in which the if formulas evaluate each field.
In the Parent Sum formula first Product Pack is checked by the if then Product then Country. So for my measure Replica of % Parent Row Total to replicate correctly the Show value % of Parent Row Total, the first field in the Rows Section has to be "Country", the second "Product" and the last one "Product Pack". 
This not convenient as I need to be able to move the fields in the Rows section the way I want. I have 9 fields which can go in the Rows section in the file I am working on.

My latest result looks like this:

I feel like I'm close. Any help would be greatly appreciated as I am really stuck on this.

Thanks in advance !

[amitchandak]

@Menar , I think you should consider isincope and hasonevalue in place of isfiltered 

Check if a level isinscope the use measure if now use another one

Both rows and column are in scope on normal cell and subtotal one of them will not be in scope. In grand total, both will not be in scope

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

[Menar]

Thank you for your response !

I tried to replace ISFILTERED by HASONEVALUE resulting in:
Parent Sum=
IF (
HASONEVALUE ( Range[Product Pack] );
CALCULATE (
SUM ( [Sales] );
ALL ( Range[Product pack] )
);
IF (
hasonevalue ( Range[Product] );
CALCULATE (
SUM ( [Sales] );
ALL ( Range[Product] )
);
CALCULATE (
SUM ( [Sales] );
ALL ( Range[Country] )
)
)
)

Yet, it still can't properly display the percentages when the fields order changes. Same issue unfortunately.

Also I tried ISINSCOPE but it didn't work seems it is not available on Excel (only in PBI ?)
Would there be a way to recreate ISINSCOPE with a complex formula ?