Forum Discussion

daxreport's avatar
daxreport
Frequent Visitor
3 years ago

Summing a column from a Virtual table

Hello, 

 

In my measure, I have created a Virtual Table using VAR statement. This virtual table uses ALLSELECTED to summarize all the columns available in the current visual along with two measures that are in the model already. In the second part of thismeasure, I would like to sum Column "A" of the virtual table I created with a filter (based on the current filter context of the matrix). However, when I try to add a SUM function for example, Column A does not even appear as an option. 

 

I have provided my first part of the measure which is a Virtual table. Struggling with the second part where I want to sum Column A based on the current filter context of the visual. Any suggestions on how to write second VAR part where it sums up Column A only for the current filter context? Thanks 

 

VAR INTERNAL_TABLE =
    CALCULATETABLE(ADDCOLUMNS (
        (SUMMARIZE(
            FACTTABLE,FACTTABLE[REGION],FACTTABLE[CUSTOMER])
        "A", [MEASURE 1]
        "B”, [MEASURE 2]
    ),ALLSELECTED())

 

 

2 Replies

  • Alf94's avatar
    Alf94
    Icon for Solution Supplier rankSolution Supplier
    3 years ago

    Hi daxreport ,

     

    Can you please try the following?

     

     

    Measure =

SUMX(
    CALCULATETABLE(
        ADDCOLUMNS (
            SUMMARIZE(
                FACTTABLE,
                FACTTABLE[REGION],
                FACTTABLE[CUSTOMER]
            )
            "A", [MEASURE 1]
            "B”, [MEASURE 2]
            ),
        ALLSELECTED()
    ),
    [A]
)

     

     

    If I answered your question, please mark my post as a solution.

     

    Best,

  • daxreport's avatar
    daxreport
    Frequent Visitor
    3 years ago

    Hi Alf94  Thanks for your response.

     

    Unfortunately, it is not working as the SUMX iteration is adding up the entire Calculated Table which is using ALLSELECTED.

     

    I need to find a way to use current filter context before SUMX conducts its operation of the calculated table. Thanks