Forum Discussion

SlavaSha7's avatar
SlavaSha7
Helper I
5 years ago
Solved

Using a virtual table to filter another table in SUMX function

Hello dears, I have a sales table with fields: * Date * ProductID * Revenue   and a Stocks table with fields: * Stock Date * ProductID * InStock_Quantity   I need to calculate year revenue...
DAX
SUMMARIZE
Virtual Table
  • SlavaSha7's avatar
    SlavaSha7
    5 years ago

    hello dear littlemojopuppy ,

    Thank you very much for your helping and efforts! I really appreciate this!

    This formula doesn't work because it displayes the same value on each day.

     

    However, I was able to workout with subquery and it works as expected now.

    So, how it works?

    I need to summarize all sales for all periods but only for Products that are in stock on a specific day

    Grand total revenue in stock only = 

Calculate(
SUMX(
  Filter(
  All(Sales),
  Sales[ProductId] in <PRODUCTS IN STOCK ON THIS DAY>
  )
 )
)

     

    "PRODUCTS IN STOCK ON THIS DAY" - is a one column table or a subquery.

    To create this subquery I use SUMMARIZE function and it works good:

      var TabStock =
      SUMMARIZE(
            Stocks,
            Stocks[ProductId],
            "Stock_QTY",
            MAXX(
                Filter(Stocks,
                Stocks[Date] = MAXX(
                                FILTER(
                                    Stocks,
                                    Stocks[Date] <= d
                                    ),
                                Stocks[Date]
                                )
                        ), 
                Stocks[Stocks])
            )

     The problem is SUMMARIZE returns a table with two columns (ProductID and Stock_QTY) and that is why the table cannot be used in "IN" clouse. 

     

    To improve this I have to make a one column table from the two columns table. To do this I use SELECTCOLUMNS function:

    var d = MAX('Date'[Date])

var TabStock = 
SELECTCOLUMNS(
    FILTER(
        //tab creation section begin
        SUMMARIZE(
            Stocks,
            Stocks[ProductId],
            "Stock_QTY",
            MAXX(
                Filter(Stocks,
                Stocks[Date] = MAXX(
                                FILTER(
                                    Stocks,
                                    Stocks[Date] <= d
                                    ),
                                Stocks[Date]
                                )
                        ), 
                Stocks[Stocks])
            ),
            //tab creation section END
        [Stock_QTY] > 0
        ),
    "ProductId",
    [ProductId]
)

     

    Now my measure with the subquery should work:

     

    Thanks a lot for your assistens and time!

     

    Best regards,

    Slava.

littlemojopuppy's avatar
littlemojopuppy
Community Champion
5 years ago

From your original post...

So...yes, I thought you needed it by date because that was the original request.

 

Furthermore, looking at just Product 1 and the $870 of revenue, that's distributed between in stock and out of stock depending on what date you happen to be looking at.

So you are looking at it by date, even if the final result may not be presented by date.

If what you are looking for is Total In Stock/Out of Stock over the course of the year...

Grand Total Revenue In Stock Only = 
    CALCULATE(
        [Revenue In Stock Only],
        ALL('Calendar')
    )

Grand Total Revenue Out of Stock Only = 
    CALCULATE(
        [Revenue Out of Stock Only],
        ALL('Calendar')
    )

 

SlavaSha7's avatar
SlavaSha7
Helper I
5 years ago

hello dear littlemojopuppy ,

Thank you very much for your helping and efforts! I really appreciate this!

This formula doesn't work because it displayes the same value on each day.

 

However, I was able to workout with subquery and it works as expected now.

So, how it works?

I need to summarize all sales for all periods but only for Products that are in stock on a specific day

Grand total revenue in stock only = 

Calculate(
SUMX(
  Filter(
  All(Sales),
  Sales[ProductId] in <PRODUCTS IN STOCK ON THIS DAY>
  )
 )
)

 

"PRODUCTS IN STOCK ON THIS DAY" - is a one column table or a subquery.

To create this subquery I use SUMMARIZE function and it works good:

  var TabStock =
      SUMMARIZE(
            Stocks,
            Stocks[ProductId],
            "Stock_QTY",
            MAXX(
                Filter(Stocks,
                Stocks[Date] = MAXX(
                                FILTER(
                                    Stocks,
                                    Stocks[Date] <= d
                                    ),
                                Stocks[Date]
                                )
                        ), 
                Stocks[Stocks])
            )

 The problem is SUMMARIZE returns a table with two columns (ProductID and Stock_QTY) and that is why the table cannot be used in "IN" clouse. 

 

To improve this I have to make a one column table from the two columns table. To do this I use SELECTCOLUMNS function:

var d = MAX('Date'[Date])

var TabStock = 
SELECTCOLUMNS(
    FILTER(
        //tab creation section begin
        SUMMARIZE(
            Stocks,
            Stocks[ProductId],
            "Stock_QTY",
            MAXX(
                Filter(Stocks,
                Stocks[Date] = MAXX(
                                FILTER(
                                    Stocks,
                                    Stocks[Date] <= d
                                    ),
                                Stocks[Date]
                                )
                        ), 
                Stocks[Stocks])
            ),
            //tab creation section END
        [Stock_QTY] > 0
        ),
    "ProductId",
    [ProductId]
)

 

Now my measure with the subquery should work:

 

Thanks a lot for your assistens and time!

 

Best regards,

Slava.