Calculate a previous level missing

[RsimonAlign]

Hi ,

Need help to calculate "missing rows".

I have a table :

Region	Country	Product	Week	Quantity
APAC	Australia	X	1	10
APAC	Australia	Y	1	12
APAC	Australia	Z	1	32
APAC	Japan	X	1	7
APAC	Hong Kong	X	1	4
APAC	Hong Kong	Y	1	13
APAC	Hong Kong	Z	1	15

The goal is to add missing rows for each country,product,week.

in the example i need to add rows for country Japan the missing products (Y,Z)

The quantity will be summerize quantity of Region by product,week

Region	Country	Product	Week	Quantity
APAC	Australia	X	1	.10
APAC	Australia	Y	1	12
APAC	Australia	Z	1	32
APAC	Japan	X	1	7
APAC	Japan	Y	1	25 (12 + 13)
APAC	Japan	Z	1	47 (32+15)

[VahidDM · Accepted solution]

Hi @RsimonAlign 

Try this code to add a new table:

New Table = 
VAR _A =
    SUMMARIZE ( 'Table', 'Table'[Region], 'Table'[Country], 'Table'[Week] )
VAR _B =
    VALUES ( 'Table'[Product] )
VAR _C =
    CROSSJOIN ( _A, _B )
RETURN
    ADDCOLUMNS (
        _C,
        "Quantity",
            IF (
                CALCULATE (
                    MAX ( 'Table'[Quantity] ),
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Country] = EARLIER ( [Country] )
                            && 'Table'[Week] = EARLIER ( [Week] )
                            && 'Table'[Product] = EARLIER ( [Product] )
                    )
                )
                    <> BLANK (),
                CALCULATE (
                    MAX ( 'Table'[Quantity] ),
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Country] = EARLIER ( [Country] )
                            && 'Table'[Week] = EARLIER ( [Week] )
                            && 'Table'[Product] = EARLIER ( [Product] )
                    )
                ),
                CALCULATE (
                    SUM ( 'Table'[Quantity] ),
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Week] = EARLIER ( [Week] )
                            && 'Table'[Product] = EARLIER ( [Product] )
                    )
                )
            )
    )

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/