Forum Discussion

RsimonAlign's avatar
RsimonAlign
Helper I
4 years ago
Solved

Calculate a previous level missing

Hi ,

Need help to calculate "missing rows".

 

I have a table :

RegionCountryProductWeekQuantity
APACAustraliaX110
APACAustraliaY112
APACAustraliaZ132
APACJapanX17
APACHong KongX14
APACHong KongY113
APACHong KongZ115

 

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

 

RegionCountryProductWeekQuantity
APACAustraliaX1.10
APACAustraliaY112
APACAustraliaZ132
APACJapanX17
APACJapanY125 (12 + 13)
APACJapanZ147 (32+15)

 

Need Help
  • VahidDM's avatar
    VahidDM
    4 years ago

    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/

     

     

1 Reply

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    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/